cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oliverblane
Frequent Visitor

Apply one slicer to different columns

I have a table similar to the following:

oliverblane_0-1637828755978.png

My aim is to have cards that show the number of assessments where the "Days" column is <= 5, with a slicer on the level (Levels 1/2/3). I have a separate Level table that I have built relationships to each of the Maths/English/Science Level columns which I slice on:

oliverblane_1-1637829229746.png

 

The model looks like the following:

oliverblane_2-1637829460903.png

Where the active relationship is Level -> English Level.

 

My DAX formulas are like:

Maths = CALCULATE(COUNT('Data'[Maths Level]), FILTER('Data', 'Data'[Maths Days] <= 5), USERELATIONSHIP('Data'[Maths Level],'Slicer'[Level])) + 0

and similar for the English and Science. However, only the English measure has the expected values when applying the slicer "Level". Am I doing something wrong? I thought that USERELATIONSHIP would make the chosen inactive relationship become active?

Sample file: https://1drv.ms/u/s!AsgvTV9XMzI1hYRb7VJx2OFLw6_z4g

 

Thank you for reading!

1 ACCEPTED SOLUTION
VahidDM
Community Champion
Community Champion

Hi @oliverblane 

 

Try these measures:

English = 
CALCULATE(
    COUNT( 'Data'[English Level] ),
    FILTER( Data, 'Data'[English Days] <= 5 ),
    USERELATIONSHIP ( 'Data'[English Level], 'Slicer'[Level] )
) + 0

 

Maths = 
CALCULATE(
    COUNT( 'Data'[Maths Level] ),
    'Data'[Maths Days] <= 5, REMOVEFILTERS(Data[English Level]),
    USERELATIONSHIP ( 'Data'[Maths Level], 'Slicer'[Level] )
) + 0

 

Science = 
CALCULATE(
    COUNT( 'Data'[Science Level] ),
     'Data'[Science Days] <= 5 ,REMOVEFILTERS(Data[English Level]),
    USERELATIONSHIP ( 'Data'[Science Level], 'Slicer'[Level] )
)

 

Output:

VahidDM_0-1637880620449.png

 

 

VahidDM_1-1637880654276.png

VahidDM_2-1637880667167.png

 

 

 

VahidDM_3-1637880682385.png

 

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

View solution in original post

9 REPLIES 9
VahidDM
Community Champion
Community Champion

Hi @oliverblane 

 

Try these measures:

English = 
CALCULATE(
    COUNT( 'Data'[English Level] ),
    FILTER( Data, 'Data'[English Days] <= 5 ),
    USERELATIONSHIP ( 'Data'[English Level], 'Slicer'[Level] )
) + 0

 

Maths = 
CALCULATE(
    COUNT( 'Data'[Maths Level] ),
    'Data'[Maths Days] <= 5, REMOVEFILTERS(Data[English Level]),
    USERELATIONSHIP ( 'Data'[Maths Level], 'Slicer'[Level] )
) + 0

 

Science = 
CALCULATE(
    COUNT( 'Data'[Science Level] ),
     'Data'[Science Days] <= 5 ,REMOVEFILTERS(Data[English Level]),
    USERELATIONSHIP ( 'Data'[Science Level], 'Slicer'[Level] )
)

 

Output:

VahidDM_0-1637880620449.png

 

 

VahidDM_1-1637880654276.png

VahidDM_2-1637880667167.png

 

 

 

VahidDM_3-1637880682385.png

 

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

View solution in original post

This worked perfectly - thank you @VahidDM!

CNENFRNL
Community Champion
Community Champion

Transform your dataset in "a DAX way", then all things get much easier,

Screenshot 2021-11-25 221049.png

 

Thank you for your reply @CNENFRNL, I agree that having the data in that format makes things a lot easier. How would I transform my original dataset to that format using DAX?

I doubt it; even if possible, the complexity is beyond imagination. Above all, DAX isn't designed for ETL process in nature; all transformation of dataset is supposed to be conducted in PQ.

VahidDM
Community Champion
Community Champion

Hi @oliverblane 

 

What is that "'Learner Info Daily" table??

 

Try this:

Maths =
CALCULATE(
    COUNT( 'Data'[Maths Level] ),
    FILTER( ALLEXCEPT('Data','Data'[Maths Level]), 'Data' <= 5 ),
    USERELATIONSHIP ( 'Data'[Maths Level], 'Slicer'[Level] )
) + 0

 

if it does not work:

Can you post sample data as text and expected output? or share a PBIX [Power BI file] after removing sensetive data?

 

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

 

Hi @VahidDM, thank you for your reply.

 

https://1drv.ms/u/s!AsgvTV9XMzI1hYRb7VJx2OFLw6_z4g

 

I have attached a PBIX file with sample data. When no slicer is selected, the output of the 3 cards should be Maths=5, English=6, Science=7, but as you will see, only English is correct. This is the same when the slicers are selected, for example when Level 1 is selected, the cards should show Maths=1, English=3, Science=2.

 

(Also, the learner info daily table was me forgetting to change a table name when writing the question - sorry!).

 

Thanks for your help.

 

 

VahidDM
Community Champion
Community Champion

@oliverblane 

 

Did you test my code for math card?

 


Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Hi @VahidDM.

 

Yes, I tried that code but unfortunately all it did was stop the measure from changing with the slicer.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.