cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Use Measure as a slicer without Unpivot Data

Hi everyone,

 

I have to re-create the sample view where % Expected Payments and % Expected Accounts are division calculations.

Slicer 1 : Month

Slicer 2: Division metrics - % Expected Payments and % Expected Accounts 

Slicer 3 & 4 : Company and Unit  

Graph : Line Chart with x axis as days, value as division calculations and legend as month

 

At present, I created another table like this:

AttributeValue
% Expected Payments1
% Expected Accounts2

 

In my data, I added two columns to hold this information and did unpivot.

 

I linked both the tables with the column Attribute and used the below DAX to do the calculations:

 

Measure = IF(CONTAINS(Table1,Table1[Attribute],"% Expected Payments"),DIVIDE(SUM(Table1[Payments]),SUM(Table1[Estimated Payments])),IF(CONTAINS(Table1,Table1[Attribute],"% Expected Accounts"),DIVIDE(SUM(Table1[Accounts]),SUM(Table1[Estimated Accounts])),0))

This solution works for me.

However,
I have millions of data and 30 attributes. This solution will increase my data rows to : Millions of Data * 30, which is a lot.
Every month, the data increases in size and I have to store that data in Power-BI Desktop.
Is there any way to achieve the same scenerio without doing the unpivot option in the actual data ? Maybe by creating different measure for each metric and proceeding somehow to get the correct calculation ?

Any help is appreciated.

 

Please find my sample solution of dummy data for your alteration here :
https://drive.google.com/open?id=1UydHHYd4iBVDTu7xoWfRoHM3OYC4lgQZ 


Thanks & Regards,
Sunaina Khera

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous ,


1. Remove all table transformations from Power Query steps.

2. Create two Measures %ExpectedAccounts  and %ExpectedAccounts

3. Disconnected Slicer with a Measure: Create a measure that will use the concept of disconnected slicer with SWITCH() function.

https://powerpivotpro.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/

 

%ExpectedAccounts =
DIVIDE (
    SUM ( 'Table1 (2)'[Accounts] ),
    SUM ( 'Table1 (2)'[Estimated Accounts] )
)
%ExpectedPayments =
DIVIDE (
    SUM ( 'Table1 (2)'[Payments] ),
    SUM ( 'Table1 (2)'[Estimated Payments] )
)
AmountMeasure =
VAR SlicerValue =
    SELECTEDVALUE ( 'Attribute Mapper'[Value] )
RETURN
    SWITCH ( SlicerValue2, [%ExpectedAccounts], 1, [%ExpectedPayments], BLANK () )


Thanks

Deepak Gupta
BI Consultant @Prodata

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

View example:Capture.PNG

Super User IV
Super User IV

@Anonymous , refer this

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Anonymous
Not applicable

Hi @amitchandak ,

 

Thanks for the revert. But, I am not sure if this post is helpful in my scenerio where I want to put together the divison measure in one slicer for the graph. If I use this approach, I can have the legends of the division metrics in the graph with the metrics as slicer but won't be able to use the month as legend.

 

Please correct me if I am wrong?

 

Hi @Anonymous ,


1. Remove all table transformations from Power Query steps.

2. Create two Measures %ExpectedAccounts  and %ExpectedAccounts

3. Disconnected Slicer with a Measure: Create a measure that will use the concept of disconnected slicer with SWITCH() function.

https://powerpivotpro.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/

 

%ExpectedAccounts =
DIVIDE (
    SUM ( 'Table1 (2)'[Accounts] ),
    SUM ( 'Table1 (2)'[Estimated Accounts] )
)
%ExpectedPayments =
DIVIDE (
    SUM ( 'Table1 (2)'[Payments] ),
    SUM ( 'Table1 (2)'[Estimated Payments] )
)
AmountMeasure =
VAR SlicerValue =
    SELECTEDVALUE ( 'Attribute Mapper'[Value] )
RETURN
    SWITCH ( SlicerValue2, [%ExpectedAccounts], 1, [%ExpectedPayments], BLANK () )


Thanks

Deepak Gupta
BI Consultant @Prodata

View solution in original post

Anonymous
Not applicable

@deepak91g , It works. Thank you so much ! 🙂

Please refer to the attached file solution https://drive.google.com/open?id=1cLPKeis3HQVft1GuexRtlEjrV8_MtFlT 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors