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

 

electrobrit's avatar
electrobrit
Post Patron
8 years ago

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