Forum Discussion

mihaita_baro's avatar
mihaita_baro
Helper II
5 years ago
Solved

Use slicer value for multiple calculation

Hi    I have 4 tables  where each one have caseload name and ID's and a date. And i want to calculate count of ID for each one of them per their case load, like in the table below.   I have also...
Tutorial Requests
  • mihaita_baro's avatar
    mihaita_baro
    5 years ago

    Hi

     

    I have managed to make it work usign the USERLATIONSHIP function in my Date table.

     

    I have another question, how can i change CASELOADNAME from all 4 table with the names of the practitioners ? Can i use some sort of script ?

     

    KPI5 = CALCULATE(COUNT(IaptRawDataKPI5[LPTID]),USERELATIONSHIP('Date'[Date],IaptRawDataKPI5[ENDDate]))
     
     

     

  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi mihaita_baro ,

     

    If you mean to create a master table for CASELOADNAME , please try:

    Table 2 = 
var t1 = DISTINCT('Table'[CASELOADNAME ])
var t2 = DISTINCT('Table (2)'[CASELOADNAME ])
var t3 = DISTINCT('Table (3)'[CASELOADNAME ])
var t4 = DISTINCT('Table (4)'[CASELOADNAME ])
return DISTINCT(UNION(t1,t2,t3,t4))

    Then create relationships through CASELOADNAME .

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

V-lianl-msft's avatar
V-lianl-msft
Community Support
5 years ago

Hi mihaita_baro ,

 

Try to disconnect date table and use it as slicer. In addition, your model design may cause other problems. Try to include CaseloadName in the main table, and then recreate the relationship.

Then try to create measure like below:

Measure = CALCULATE(COUNT(ABC[XXX]),FILTER(ABC,ABC[DATE] IN VALUES(DATE[DATE])))

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

mihaita_baro's avatar
mihaita_baro
Helper II
5 years ago

Hi

 

I have managed to make it work usign the USERLATIONSHIP function in my Date table.

 

I have another question, how can i change CASELOADNAME from all 4 table with the names of the practitioners ? Can i use some sort of script ?

 

KPI5 = CALCULATE(COUNT(IaptRawDataKPI5[LPTID]),USERELATIONSHIP('Date'[Date],IaptRawDataKPI5[ENDDate]))
 
 

 

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    Hi mihaita_baro ,

     

    If you mean to create a master table for CASELOADNAME , please try:

    Table 2 = 
var t1 = DISTINCT('Table'[CASELOADNAME ])
var t2 = DISTINCT('Table (2)'[CASELOADNAME ])
var t3 = DISTINCT('Table (3)'[CASELOADNAME ])
var t4 = DISTINCT('Table (4)'[CASELOADNAME ])
return DISTINCT(UNION(t1,t2,t3,t4))

    Then create relationships through CASELOADNAME .

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.