Multiple measures based on multiple date columns

[GarryFarrell]

How should I model this in Power BI? I need 4 measures that are dependent on different date columns. So far I have loaded the data into 4 different fact tables, which duplicates the dimension data, and then joined the dates to a date dimension. Then I have to create dimension tables by geting a distinct list of each of the dimension columns and join to each of the fact tables.

So my measures are now of this format. Total1 = CALCULATE(sum(Table1[No. of Items]),Table1[Date1])

Is there a way to create a DAX formula to create the measure and allow me join to the Date Dimension or do month filtering? Or is the answer to unpivot the data? My issues is the duplication of the dimension data.

Date dimension on the left, fact tables in the middle, dimension tables on the right.

[v-shex-msft · Accepted solution]

Hi GarryFarrell,

You can simply achieve your requirement on use unpivot data function, below is the sample:

Create table based on your screenshot:

Unpivot table:

Add measures:

Measure1 = SUMX(FILTER(ALLSELECTED('Dimension Item Unpivot'),'Dimension Item Unpivot'[Attribute]="Date1"),'Dimension Item Unpivot'[No of Items])

Measure2 = SUMX(FILTER(ALLSELECTED('Dimension Item Unpivot'),'Dimension Item Unpivot'[Attribute]="Date2"),'Dimension Item Unpivot'[No of Items])

Measure3 = SUMX(FILTER(ALLSELECTED('Dimension Item Unpivot'),'Dimension Item Unpivot'[Attribute]="Date3"),'Dimension Item Unpivot'[No of Items])

Measure4 = SUMX(FILTER(ALLSELECTED('Dimension Item Unpivot'),'Dimension Item Unpivot'[Attribute]="Date4"),'Dimension Item Unpivot'[No of Items])

Create visuals.

Table visual:

Slicer:

Card visuals:

Result:

Regards,

Xiaoxin Sheng

[electrobrit]

@v-shex-msftI could use this (SO desperately) but I can't figure out what you've done with your unpivoted columns. It is another table?

I see where you create visuals it has 2 tables "Dimension Item" and "Dimension Item Unpivoted"

When I did my unpivot columns, it did not do this, it just added a query step so there is a disconnect between that step and creating the visuals.

Also, to do this would I set up a relationship to a date table like the first person (gary) did before I do everything else.

Can you tell me what you have done?

thank you both for this!

[GarryFarrell]

Hi Xiaoxin,

Thanks for the effort you may do re-create the issue. Do you think you could write a DAX formula to make the measures with out the unpivot?

Regards,

Garry

[v-shex-msft]

Hi GarryFarrell,

>> Do you think you could write a DAX formula to make the measures with out the unpivot?

It is possible, you can follow below steps:

1. create a datetable contain the date from date1, date2, date3, date4.

DateTable = DISTINCT(UNION(VALUES('Dimension Item'[Date1]),VALUES('Dimension Item'[Date2]),VALUES('Dimension Item'[Date3]),VALUES('Dimension Item'[Date4])))

2. Write a measure to get the current select item from the slicer.

Select Date = if(HASONEVALUE(DateTable[Date1]),SUM(DateTable[Date1]),BLANK())

3. Write measures to calculate the total of date.

Sum of Date1 = SUMX(FILTER('Dimension Item','Dimension Item'[Date1]=[Select Date]),'Dimension Item'[No of Items])

Sum of Date2 = SUMX(FILTER('Dimension Item','Dimension Item'[Date2]=[Select Date]),'Dimension Item'[No of Items])

Sum of Date3 = SUMX(FILTER('Dimension Item','Dimension Item'[Date3]=[Select Date]),'Dimension Item'[No of Items])

Sum of Date4 = SUMX(FILTER('Dimension Item','Dimension Item'[Date4]=[Select Date]),'Dimension Item'[No of Items])

4. Create visuals.

 

Notice: this measure only worked when you choose one date each time in the slicer, if you select multiple dates, it doesn’t work.

Regards,

Xiaoxin Sheng