DAX | Lookup Value from Rows in Unrelated Tables

PHOTO

Fri Dec 08 2023 09:49:57 GMT+0000 (Coordinated Universal Time)

Saved by @nikahafiz #ms.pbi #dax #dax.lookupvalue

LOOKUPVALUE(
    Products[UnitPrice],
    PRODUCTS[ProductID], ORDER_DETAILS[ProductID]
        )

COPY

Lookup the related Product_Cost using LOOKUPVALUE, matching on the ProductID column in both tables.

Save snippets that work from anywhere online with our extensions

Comments

dax

@nikahafiz

#dax #tabular.editor #live.dataset #ms.pbi #dataset

Showing DAX Expressions When Using Power BI Datasets

foreach (var m in Model.AllMeasures) {
  m.Description = m.Expression;
}

#ms.pbi #dax #dax.calculate #dax.filter #dax.all #dax.in

DAX | Calculate Revenue Across Multiple Years

CALCULATE(
    [Revenue],
    FILTER(
        ALL( 'Calendar'[Year] ),
        'Calendar'[Year] IN { 1996, 1998 }
    )
)

#ms.pbi #dax #dax.lookupvalue

DAX | Lookup Value from Rows in Unrelated Tables

LOOKUPVALUE(
    Products[UnitPrice],
    PRODUCTS[ProductID], ORDER_DETAILS[ProductID]
        )

#ms.pbi #dax #calendar.table #dax.addcolumns #dax.var #dax.return #dax.format #dax.month

DAX | Creating Calendar Table Using CALENDARAUTO

Calendar = 
  
VAR CalTable = CALENDARAUTO ()
VAR MyCal =
    ADDCOLUMNS (
        CalTable,
        "Index", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "mmm" ),
        "Qtr", FORMAT ( [Date], "\QQ" ),
        "Year", FORMAT ( [Date], "yyyy" )
    )
RETURN
    MyCal

#ms.pbi #dax #calendar.table #dax.quarter

DAX | Quarter No - 1

QtrNo = 

"Q" & QUARTER( 'Calendar'[Date] )

#ms.pbi #dax #calendar.table #dax.weekday

DAX | Week End Date

Week EndDate = 

'Calendar'[Date] + 7-1*WEEKDAY( 'Calendar'[Date], 2 )

#ms.pbi #dax #calendar.table #dax.weekday

DAX | Week Start Date - 1 (Week Begins on Monday)

Week StartDate = 

'Calendar'[Date] + -1*WEEKDAY( 'Calendar'[Date], 2 ) + 1

#ms.pbi #dax #calendar.table #dax.rankx #dax.asc #dax.dense #ranking #week.rank

DAX | Week Ranking

Week Rank = 

RANKX(
    ALL( 'Calendar' ),
    'Calendar'[Week StartDate], , ASC, Dense)

#ms.pbi #dax #calendar.table #dax.weeknum

DAX | Week No - 1

WeekNo = 

"W" & WEEKNUM( 'Calendar'[Date], 2 )

#ms.pbi #dax #calendar.table #dax.format #dax.calendar #format-ddd

DAX | Day Name

DayName = 

FORMAT ( 'Calendar'[Date], "ddd" )

#ms.pbi #dax #dax.weekday #calendar.table

DAX | Day of Week No

DayofWeekNo = 
WEEKDAY ( [Date], 2 )

#ms.pbi #dax #calendar.table #dax.day

DAX | Day of Month No

DayofMonthNo =

DAY ( 'Calendar'[Date] )

#ms.pbi #dax #calendar.table #week.no

DAX | Year + WeekNo

YearWeekNo = 

'Calendar'[Year]*100 + 'Calendar'[WeekNo]

#ms.pbi #dax #calendar.table #dax.switch #dax.true #dax.month #quarter

DAX | Quarter No - 2

Qtr =

SWITCH(
	TRUE(),
	MONTH('Calendar[Date]') <=3, "Q1",
	MONTH('Calendar[Date]') <=6, "Q2",
	MONTH('Calendar[Date]') <=9, "Q3",
	"Q4"
)

#ms.pbi #dax #dax.topn #ranking

DAX | Best Selling Day/Date - 1

Best Selling Day/Date =

TOPN(
	1,
	'Calendar',
	[Total Sales]
)

DAX | Lookup Value from Rows in Unrelated Tables

Save snippets that work from anywhere online with our extensions

Comments

More like this

dax

Browse more snippets >>

DAX | Lookup Value from Rows in Unrelated Tables

Save snippets that work from anywhere online with our extensions

Comments

More like this

dax

Browse more snippets >>

Embed code snippet