Forum Discussion

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
3 years ago
hi Hong_HW
Try like:
Measure =
IF(
SELECTEDVALUE(Table1[Country]) = "CTY",
SUMX(ALL(Table2),Table2[USD]),
SUM(Table2[USD])
)

Frequent Visitor

3 years ago

Some add on information

Table 1

Table 2

Currently when i select the individual country, the sales amount is showing up.

But when i select CTY, the sales amount is showing blank. I'm not sure if there is any dax could help me to sum up the sales amount from all countries? Much appreciate it.

FreemanZ
Super 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
  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
    Super 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".

Related content