Sample unification

[Veronika3]

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) 

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

))

B)

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?

[v-sshirivolu · Accepted solution]

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. 

[Veronika3]

Thanky you, it works :).

[Veronika3]

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.

[amitchandak]

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

[amitchandak]

@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

[Veronika3]

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

[amitchandak]

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