Using field parameters to get a count of applicable measures by a field

[Anonymous]

Hallo guys,

I want to calculate how many times my “Tips ”measures are applicable by my customers.

I have the following dataset:

This is the fact table:

Customers	Profit	Cost	Bank balance
A	10.000	5.000	20.000
B	11.000	3.000	30.000
C	15.000	1.000	100.000
D	4.000	2.000	10.000
E	2.000	200	5.000

this is the dim table:

Customers	Name
A	John
B	Jane
C	Joe
D	Jennie
E	Janneth

Here are some examples of my “Tips” measures:

Tip - profit = if(sum(Customer data[Profit]) >= 10.000, “Congratulation with 10K plus profit”, blank())

Tip – cost = if(sum(Customer data[Bank balance]) >= 25.000, “Try to reinvest your money”, blank())

I want to create a page where to total amount of tips is visible. So I can select via a Parameter the tip “Tip - Profit” which shows me in a visual the total time this tip is applicable. And in another matrix shows me the customers for which this tip is applicable.

My desired result would be.

Filter on parameter “Tip profit”
visual shows me: 3

Matrix visual shows me:

Customer A
Customer B

Customer C

Thank you in advance!

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. create a table and add it to slicer visual
Table:

2. create below four measure

Tip Cost =
VAR tmp =
    FILTER ( ALL ( 'Fact' ), 'Fact'[Bank balance] >= 25000 )
RETURN
    COUNTROWS ( tmp )

Tip Profit =
VAR tmp =
    FILTER ( ALL ( 'Fact' ), 'Fact'[Profit] >= 10000 )
RETURN
    COUNTROWS ( tmp )

Measure =
VAR cur_tip =
    SELECTEDVALUE ( 'Table'[Tip] )
RETURN
    SWITCH (
        TRUE (),
        cur_tip = "Tip-cost",
            "Filter on parameter:Tip cost" & "
" & "visual shows me: " & [Tip Cost],
        cur_tip = "Tip-profit",
            "Filter on parameter:Tip profit" & "
" & "visual shows me: " & [Tip Profit]
    )

Measure2 =
VAR cur_tip =
    SELECTEDVALUE ( 'Table'[Tip] )
VAR cur_customer =
    SELECTEDVALUE ( 'Fact'[Customers] )
VAR tmp =
    FILTER ( ALL ( 'Fact' ), 'Fact'[Profit] >= 10000 )
VAR tmp1 =
    FILTER ( ALL ( 'Fact' ), 'Fact'[Bank balance] >= 25000 )
RETURN
    SWITCH (
        cur_tip,
        "Tip-profit", IF ( CONTAINS ( tmp, [Customers], cur_customer ), 1, 0 ),
        "Tip-cost", IF ( CONTAINS ( tmp1, [Customers], cur_customer ), 1, 0 )
    )

3. add a card visual with a measure named "Measure"

4. add a table visual visual with "Fact[Customers]" column

5. add the measure named "Measure 2" to table visual filter pane and set it "is 1"

Now you can see below :

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.