The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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
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
@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])))
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
77 | |
76 | |
43 | |
37 |
User | Count |
---|---|
157 | |
114 | |
64 | |
60 | |
55 |