Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GarryFarrell
Advocate III
Advocate III

Multiple measures based on multiple date columns

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.

 

Sample Data.PNG

 

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

Data Model.PNG

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi GarryFarrell,

 

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

 

Create table based on your screenshot:

Capture3.PNG

 

Unpivot table:

Capture9.PNG

 

Capture4.PNG

 

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:

Capture5.PNG

 

Slicer:

Capture6.PNG

 

Card visuals:

Capture7.PNG

 

Result:

Capture8.PNG

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi GarryFarrell,

 

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

 

Create table based on your screenshot:

Capture3.PNG

 

Unpivot table:

Capture9.PNG

 

Capture4.PNG

 

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:

Capture5.PNG

 

Slicer:

Capture6.PNG

 

Card visuals:

Capture7.PNG

 

Result:

Capture8.PNG

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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!

 

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

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])))

Capture3.PNG

 

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.

 Capture4.PNG

 

Capture5.PNG

 

Capture6.PNG

 

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.