topic Re: Sample unification in DAX Commands and Tips

Sample unification

Veronika3 — Wed, 19 Nov 2025 11:01:00 GMT

Hi,

I need to know if this formula can be consolidated. I've tried to consolidate it, but when I compared the results, my formula turned out to be incorrect.

IF(ISFILTERED(factNewBrand[PrimaryCategoryName]) && (ISFILTERED(dimBrand[BrandName]) || ISFILTERED(dimBrand[ManufacturerName])),

SUMX(DISTINCT(factNewBrand[KeyB]),FIRSTNONBLANK(factNewBrand[CountCustomersCB],0)),

IF(ISFILTERED(factNewBrand[PrimaryCategoryName]) ,

SUMX(DISTINCT(factNewBrand[KeyC]),FIRSTNONBLANK(factNewBrand[CountCustomersC],0)),

IF(ISFILTERED(dimBrand[BrandName]) ,

SUMX(DISTINCT(factNewBrand[KeyB]),FIRSTNONBLANK(factNewBrand[CountCustomersB],0)),

SUMX(DISTINCT(factNewBrand[KeyB]),FIRSTNONBLANK(factNewBrand[CountCustomersB],0)))

))

IF(ISFILTERED(factNewBrand[PrimaryCategoryName]) && ISFILTERED(dimBrand[BrandName]),

SUMX(DISTINCT(factNewBrand[Key]),FIRSTNONBLANK(factNewBrand[CountCustomers],0)),

IF(ISFILTERED(factNewBrand[PrimaryCategoryName]) ,

SUMX(DISTINCT(factNewBrand[KeyC]),FIRSTNONBLANK(factNewBrand[CountCustomersC],0)),

IF(ISFILTERED(dimBrand[BrandName]) ,

SUMX(DISTINCT(factNewBrand[KeyB]),FIRSTNONBLANK(factNewBrand[CountCustomersB],0)),

SUMX(DISTINCT(factNewBrand[Keyc]),FIRSTNONBLANK(factNewBrand[CountCustomersc],0)))))

My wrong result:

IF(ISFILTERED(factNewBrand[PrimaryCategoryName]) && (ISFILTERED(dimBrand[BrandName]) || ISFILTERED(dimBrand[ManufacturerName])),

SUMX(DISTINCT(factNewBrand[Key]), FIRSTNONBLANK(factNewBrand[CountCustomers], 0)),

IF(

ISFILTERED(factNewBrand[PrimaryCategoryName]),

SUMX(DISTINCT(factNewBrand[KeyB]), FIRSTNONBLANK(factNewBrand[CountCustomersCB], 0)),

IF(

ISFILTERED(factNewBrand[PrimaryCategoryName]),

SUMX(DISTINCT(factNewBrand[KeyC]), FIRSTNONBLANK(factNewBrand[CountCustomersC], 0)),

IF(

ISFILTERED(dimBrand[BrandName]) || ISFILTERED(dimBrand[ManufacturerName]),

SUMX(DISTINCT(factNewBrand[KeyB]), FIRSTNONBLANK(factNewBrand[CountCustomersB], 0)),

SUMX(DISTINCT(factNewBrand[Keyc]), FIRSTNONBLANK(factNewBrand[CountCustomersc], 0))

)

))

Can you help me, please?

Re: Sample unification

amitchandak — Wed, 19 Nov 2025 11:43:37 GMT

Veronika3 , I usually prefer isincope from the last/leaf level to up

Change as per need

Switch( true(),
ISINSCOPE( dimBrand[ManufacturerName]), SUMX(Values( factNewBrand[Key] ), FIRSTNONBLANK( factNewBrand[CountCustomersA], 0 ) ),
ISINSCOPE( dimBrand[BrandName]), SUMX(Values( factNewBrand[Key] ), FIRSTNONBLANK( factNewBrand[CountCustomersB], 0 ) ),
ISINSCOPE( factNewBrand[PrimaryCategoryName] ), SUMX(Values( factNewBrand[Key] ), FIRSTNONBLANK( factNewBrand[CountCustomersC], 0 ) ),
SUMX(Values( factNewBrand[Key] ), FIRSTNONBLANK( factNewBrand[CountCustomers], 0 ) ))

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA

Re: Sample unification

Veronika3 — Wed, 19 Nov 2025 11:55:46 GMT

Thank you for your answear but the result is still wrong. If I choose ManufactureName and BrandName, the number is very high. (NC First Combined is the new metric).

Re: Sample unification

amitchandak — Wed, 19 Nov 2025 12:17:04 GMT

Veronika3 , Please let me know the logic that you are looking for

Re: Sample unification

Veronika3 — Wed, 19 Nov 2025 12:43:40 GMT

the logic is as follows:
NC first B and NC first C are new customers according to a certain key, which is defined in the data source.
For NC first B there are two conditions and two calculations, if neither condition is met, a filter is performed according to the unique values of KeyB according to CustomersB.
NC first C has its own conditions, which are used to perform calculations according to the conditions. If no condition is met, a unique KeyC according to the unique values of CountCustomersC is included.
Unfortunately, NC First B calculates conditions with unique keys KeyB and CountCustomerCB and CountCustomerB, but NC First C calculates with KeyC, CountCustomerB and CountCustomerC.

Re: Sample unification

amitchandak — Wed, 19 Nov 2025 12:52:38 GMT

Veronika3 , Create these two and try second one in visual

NC First Consolidated =
VAR hasCategory = ISFILTERED( factNewBrand[PrimaryCategoryName] )
VAR hasBrand = ISFILTERED( dimBrand[BrandName] )
VAR hasManufacturer = ISFILTERED( dimBrand[ManufacturerName] )
VAR useBoth = hasCategory && ( hasBrand || hasManufacturer )

RETURN
SWITCH(
TRUE(),
useBoth, SUMX(Values( factNewBrand[KeyB] ), FIRSTNONBLANK( factNewBrand[CountCustomersCB], 0 )),
hasCategory,SUMX( Values( factNewBrand[KeyC] ),FIRSTNONBLANK( factNewBrand[CountCustomersC], 0 ) ),
hasBrand || hasManufacturer,SUMX(Values( factNewBrand[KeyB] ),FIRSTNONBLANK( factNewBrand[CountCustomersB], 0 ) ),
SUMX(DISTINCT( factNewBrand[KeyC] ),FIRSTNONBLANK( factNewBrand[CountCustomersC], 0 ))
)

NC First Consolidated Total = Sumx(Summarize(factNewBrand, factNewBrand[KeyB] , factNewBrand[KeyC]) , [NC First Consolidated])

Re: Sample unification

Anonymous — Thu, 20 Nov 2025 09:12:59 GMT

Hi Veronika3 ,

This version fully consolidates the original formulas and handles all filter combinations correctly. The only adjustment needed was in the no filter fallback, where the original logic used the lowercase keyc and CountCustomersc columns. After aligning that part, the consolidated SWITCH version matches the behavior of formulas A and B in every scenario and removes the repeated conditions from the original expressions.

Try this version, which fully consolidates the original logic and matches all filter scenarios -

NC First Consolidated =
VAR hasCat = ISFILTERED(factNewBrand[PrimaryCategoryName])
VAR hasBrand = ISFILTERED(dimBrand[BrandName])
VAR hasManu = ISFILTERED(dimBrand[ManufacturerName])
VAR useBoth = hasCat && (hasBrand || hasManu)

RETURN
SWITCH(
TRUE(),

useBoth,
SUMX(
DISTINCT(factNewBrand[KeyB]),
FIRSTNONBLANK(factNewBrand[CountCustomersCB], 0)
),

hasCat,
SUMX(
DISTINCT(factNewBrand[KeyC]),
FIRSTNONBLANK(factNewBrand[CountCustomersC], 0)
),

hasBrand || hasManu,
SUMX(
DISTINCT(factNewBrand[KeyB]),
FIRSTNONBLANK(factNewBrand[CountCustomersB], 0)
),

-- Default (no filters) – matches original formula B exactly
SUMX(
DISTINCT(factNewBrand[Keyc]),
FIRSTNONBLANK(factNewBrand[CountCustomersc], 0)
)
)

Thanks.

Re: Sample unification

Veronika3 — Thu, 20 Nov 2025 13:40:14 GMT

Thanky you, it works :).