topic Need help on filter dax in DAX Commands and Tips

Need help on filter dax

Hong_HW — Fri, 24 Feb 2023 04:50:41 GMT

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

Table 2 named Sales Amt

Country	USD
PH	300
MY	200
CN	150
HK	200

In my PBI, i have the country as the filter, now i want to create a table to return the corresponding sales amount by selecting the options from the country filter.

i.e Select CTY, return 850 ( which is the sum of SG+MY+CN+HK)

Select ASEAN, result table return 500 (which is the sum of PH+MY)

Select NASIA, result table return 350 (which is the sum of CN+HK)

Select PH, only return 300;

Much appreciate it.

Thank you

Regards

Hazel

Re: Need help on filter dax

FreemanZ — Fri, 24 Feb 2023 05:19:38 GMT

hi Hong_HW

in table1, is TH actually PH?

What does CTY mean? (The others stand for countries or regions)

ASEAN and NASIA are not countries, so they could not in the same column as PH, right?

Re: Need help on filter dax

Hong_HW — Fri, 24 Feb 2023 06:42:18 GMT

oh, yes there is typo , TH is actually PH.

CTY means for all countries sum up

Re: Need help on filter dax

Hong_HW — Fri, 24 Feb 2023 10:48:43 GMT

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.

Re: Need help on filter dax

FreemanZ — Fri, 24 Feb 2023 11:22:56 GMT

hi Hong_HW

Try like:

Measure =

IF(

SELECTEDVALUE(Table1[Country]) = "CTY",

SUMX(ALL(Table2),Table2[USD]),

SUM(Table2[USD])

)

Re: Need help on filter dax

Hong_HW — Fri, 24 Feb 2023 12:46:07 GMT

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)
)

Re: Need help on filter dax

FreemanZ — Sat, 25 Feb 2023 00:27:26 GMT

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