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 created a Date table and i want to create a model.

 

Basically when a date is selected i want to count the number of ID's for each table . i was trying to get the slicer value and when selectedvalues = ENDdate to count id's but is not working

 

CaseloadNameReferral countIaptRawDataKPI4 countIaptRawDataKPI5IaptRawDataKPI5
Caseload 110567834
Caseload 234345432

 

 

 

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.

     

5 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago

    not quite clear what you doing, firstly how are all these tables joined?  

    • mihaita_baro's avatar
      mihaita_baro
      Helper II
      5 years ago

      Whith this model i am only getting first two figures right, the other are incorrect

       

       

       

       

       

  • 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.