DAX Measure to display 2 different KPI with different tables

[Silvia_GB]

Hi,

I have a tricky issue with Power BI that I am being struggling and not knowing how to solve. I am trying to use a KPI vsz for different KPIs, all together. So, I have different tables,

First table called FY 2025 has different columns: distributor, country, product, and Total Pts Target (measure I created to know my patients target at the end of 2025)

Total Pts Target = 

CALCULATE(SUM('FY 2025'[Total Patients]), KEEPFILTERS(ABS('FY 2025'[Distributor] = "Abba")))

Note that the Distributor is linked to the concrete countries

Second table called Total Patients Quarters has different columns: country, product and Current Pts (measure I created to know the current patients for each quarter, for each country and product and actually only does it for the first Product and first Quarter).

Selected Patientss =

VAR SelectedQuarter = SELECTEDVALUE('Total Patients Quarters'[Quarters])

VAR Result =

    SWITCH(

        TRUE(),

        SelectedQuarter = "Q1 2025", MAX('Total Patients2'[Q1 2025]),

        SelectedQuarter = "Q2 2025", MAX('Total Patients2'[Q2 2025]),

        SelectedQuarter = "Q3 2025", MAX('Total Patients2'[Q3 2025]),

        SelectedQuarter = "Q4 2025", MAX('Total Patients2'[Q4 2025]),

        BLANK()

    )

RETURN

    Result

So my problem is that I have two filters on the page (quarters and products), and I want that when I filter by quarter and product displays the total patients target and the current pts for each quarter (cause then I want to calculate a measure to see how far I am in each quarter from the target, based on my current patients on each quarter).

The issue here is that the categroy country doesn't match for each table cause for the first table called Total Pts Target I have 40 rows (each row display Estonia, Product A, Lithuania Product A, and so on, for 5 countries 8 products). But for the second table I have 160 rows, cause I have the Quarters as columns, and I have to multiply the group of 74 rows 4 times, (cause I have 4 quarters). So on the vsz it only allows to use one category, so depending on which country I use, I get one thing or the other displayed (total target patients or current pts), not both

I also tried to unify both tables with the quarters and the patients per month, but it was quite complicated as well, and just worked out for the first product and country.

Hope someone could give me a hand, I can provide pictures if needed it

Thanks,

Silvia

[Silvia_GB]

@Selva-Salimi thanks for your answer but didnt work out for me. Please find below my tables, I think maybe will be easier to understand with images:

FY 2025 Table looks like this:

This table has for each of my 5 countries (Estonia, Lithuania, Latvia, Hungary, Bulgaria) for the 8 products the total patients that I need to get at the end of 2025. This is captured on the measure that I created Total Pts Target (shared before), that as yous said is based on the relationships, so basically filters out each country when put it on the KPI vsz cards.

The second table that I have is the Total Patients Quarters, and looks like this (no exhaustive, since it's quite big):

Which shows for each country again, for the 8 products the corresponding number of current patients for each quarter (Q1 2025, Q2 2025, Q3 2025, and Q4 2025). I tried to create a measure that does the same as the first one but gathering the number of N patients for each quarter, country and product.

My main objective here is to be able to put both measures in one single KPI card vsz so I can see my final target pts and the current pts for each month when filtering out by quarters and products (filter that I put out of this last table as a slicer in the page). The big problem here is that in both tables the length of the country category is not the same, since for the quarters I just duplicated 4 times (4 quarters)

Hope you or someone else can help me with that

[Selva-Salimi]

@Silvia_GB ,

I’m still not entirely clear on what you're aiming to achieve, but based on my understanding, I believe you may just need to modify your measure as follows:

Selected Patientss =

VAR SelectedQuarter = SELECTEDVALUE('Total Patients Quarters'[Quarters])

VAR Result =

    SWITCH(

        TRUE(),

        SelectedQuarter = "Q1 2025", Sum('Total Patients2'[Q1 2025]),

        SelectedQuarter = "Q2 2025", sum('Total Patients2'[Q2 2025]),

        SelectedQuarter = "Q3 2025", sum('Total Patients2'[Q3 2025]),

        SelectedQuarter = "Q4 2025", sum('Total Patients2'[Q4 2025]),

        BLANK()

    )

RETURN

    Result

If this solution doesn’t work, could you please clarify your question with an example of what you're expecting to see?

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[Silvia_GB]

Hi @Selva-Salimi no, it doesnt work cause I don't want to do the sum up for the quarterly patients, I want to select the concrete value, for each country, quarter and product, that's why I use the MAX function. Please see below a snippet of my dashboard:

So I have the Quarters and Product filters at the top, and what I want to display on the 5 KPIs card, one for each country, is the total target patients which is the big number, and then at the right corner the current pts (N Patietns from my Total Patients Quarter table). So, when I change the quarter, for example Q2 I want to see still the same total target pts which for Bulgaria is 203, but then the right corner number which refers to the quarterly patients should be 501, now is 414 cause is filtering for Q1 2025, Product 1.

This only works with Bulgaria Product 1 cause I assume that goes alphabetically and the measure just does it for the first one, forgetting the other ones in the table

Hope is clearer now, thanks for trying to help me out.

[Selva-Salimi · Accepted solution]

@Silvia_GB ,

Ok, thanks, I think that I understand the problem now. then you can write your measure as follows:

Measure :=

VAR Selectedproduct = SELECTEDVALUE('FY 2025'[product])

return

calculate( max('Total Patients Quarters'[n pateients]), filter ('Total Patients Quarters','Total Patients Quarters'[product]=Selectedproduct))

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[Silvia_GB]

That worked, that's what I was trying to do.

Thanks a lot for your help, much appreciated!

[Selva-Salimi]

Hi @Silvia_GB 

I recommend you to not to use relationship between these two table and instead use "selectedvalue" or "values" function in your measure to calculate the target.
if it doesn't help you can upload pictures to clarify the problem.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.