Forum Discussion
Sample unification
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.
A)
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.
7 Replies
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.Thanky you, it works :).
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/smhIPw3OkKAThank 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).
Veronika3 , Please let me know the logic that you are looking for
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.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])
