Forum Discussion

dandamudisanjay's avatar
dandamudisanjay
Regular Visitor
5 years ago

Help with DAX

Hi Experts, 

 

Hope you all are doing well. 

 

I'm hoping to get some help with the following issue. 

 

We have two tables, table 1 and table 2 connected with 1-N relation  with fields as below

 

Table 1

ID
Category
User



Table 2

KPI
User

 

Users have different KPI's and KPI is linked to category. we need to display total of ID's once we select the user so the relevant KPI (in text) gets selected behind the scenes. 

 

Please could you share any ideas?

 

Thanks in advance. 

Sanjay

@Dax
@dax @help

7 Replies

  • Fsciencetech's avatar
    Fsciencetech
    Helper III
    5 years ago

    Hi dandamudisanjay 

    Hope you are good.

     

    If you have two table, so why you are not making relationship between them with "Many to one"

     

    you may get the related KPI's .

    If it was not relavent, share few records for both table, will reply soon on that.

     

    • dandamudisanjay's avatar
      dandamudisanjay
      Regular Visitor
      5 years ago

      Hi Fsciencetech 

       

      I'm looking to get total of ID's from table 1 which meets the following conditions. 

       

      - if KPI = "Target1" then count of ID's where category is "category 1"

      - if KPI = "Target2" then count of ID's where category is "category 2"

      - else, total count of ID's

      • SivaMani's avatar
        SivaMani
        Resident Rockstar
        5 years ago

        dandamudisanjay,

        Try this in a measure,

        Count of Id =
VAR __KPI =
    MAX ( Table2[KPI] )
RETURN
    SWITCH (
        TRUE (),
        __KPI = "Target1", CALCULATE ( COUNT ( Table1[Id] ), Table1[Category] = "category 1" ),
        __KPI = "Target2", CALCULATE ( COUNT ( Table1[Id] ), Table1[Category] = "category 2" ),
        COUNT ( Table1[Id] )
    )

         Note: You may need to change the cross-filtering as bidirectional 

    • dandamudisanjay's avatar
      dandamudisanjay
      Regular Visitor
      5 years ago

      Hi SivaMani ,

       

      I'm looking to get total of ID's from table 1 which meets the following conditions. 

       

       

      - if KPI = "Target1" then count of ID's where category is "category 1"

      - if KPI = "Target2" then count of ID's where category is "category 2"

      - else, total count of ID's