Forum Discussion

Veronika3's avatar
Veronika3
Regular Visitor
9 months ago
Solved

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)     IF(ISFILTERED(factNewBrand...
  • v-sshirivolu's avatar
    v-sshirivolu
    9 months ago

    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's avatar
Veronika3
Regular Visitor
9 months ago

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's avatar
amitchandak
Super User
9 months ago

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