Forum Discussion

GarryFarrell's avatar
GarryFarrell
Advocate III
9 years ago
Solved

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, a...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

 

GarryFarrell's avatar
GarryFarrell
Advocate III
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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