Forum Discussion

Hong_HW's avatar
Hong_HW
Frequent Visitor
3 years ago
Solved

Need help on filter dax

Hi, I'll need some help on the dax formula.   I have 2 tables below Table 1 named Country with 2 columns Country rollup Hub CTY CTY TH ASEAN MY ASEAN CN NASIA HK NASIA ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Hong_HW 

    Try like:
    Measure =
    IF(
       SELECTEDVALUE(Table1[Country]) = "CTY",
       SUMX(ALL(Table2),Table2[USD]),
       SUM(Table2[USD])
    )
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi Hong_HW 

Try like:
Measure =
IF(
   SELECTEDVALUE(Table1[Country]) = "CTY",
   SUMX(ALL(Table2),Table2[USD]),
   SUM(Table2[USD])
)
Hong_HW's avatar
Hong_HW
Frequent Visitor
3 years ago

Thank you very much, it works 👍.

 

I've tried the following formula, but it doesn't work. Not sure what is the issue.  It will be much appreated if you could advise too?

 

Test Measure 1 =
VAR SelectedCountry = SELECTEDVALUE('Table 1'[Country rollup])
VAR SelectedHub = SELECTEDVALUE('Table 1'[Hub])

RETURN
IF(
SelectedHub = "CTY",
CALCULATE(
SUM('Table 2'[Amount]),'Table 2'[Country] in {"PH","MY","CN","HK"}),

CALCULATE(
SUM('Table 2'[Amount]),
'Table 2'[Country] = SelectedCountry)
)

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Hong_HW 

    it seems, this part:

     

    SelectedHub = "CTY",
    CALCULATE(
    SUM('Table 2'[Amount]),'Table 2'[Country] in {"PH","MY","CN","HK"})

     

    will return blank, as there is no row where hub is CTY and country is any of "PH","MY","CN","HK".