Forum Discussion

blai's avatar
blai
Frequent Visitor
2 years ago

Column or measure Conditionals

Hi, I have a column with this conditions:    ConditionalColumn= VAR userID= [UserID] VAR IDduplicated = CALCULATE(COUNTROWS(table), ALLEXCEPT(table, table[UserID])) > 1 VAR shopIDduplicated = ...
Conditional Formula
Conditional measures
Conditionals
ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
2 years ago

blai , you can play with these calculated columns to achieve your goal. Since the end date is earlier then start date, I had to change their placement in DATEDIFF formula.

level0 = 
VAR userIDs = CALCULATE ( COUNT ( t[userID] ), ALLEXCEPT ( t, t[userID] ) )
RETURN
    IF ( userIDs > 1, "Yes", "No" )
level1 = 
VAR shopIDs = CALCULATE ( COUNT ( t[shopID] ), ALLEXCEPT ( t, t[userID], t[shopID] ) )
RETURN
    IF ( shopIDs > 1, "Yes", "No" )
level2 = 
VAR categoryIDs = CALCULATE ( COUNT ( t[categoryID] ), ALLEXCEPT ( t, t[userID], t[shopID], t[categoryID] ) )
RETURN
    IF ( categoryIDs > 1, "Yes", "No" )
level3 =
VAR categoryIDs =
    CALCULATE (
        COUNT ( t[categoryID] ),
        ALLEXCEPT ( t, t[userID], t[shopID], t[categoryID] )
    )
VAR dt_start =
    IF (
        NOT ISBLANK ( t[YearStart] ),
        DATEVALUE ( t[YearStart] & "/" & t[MonthStart] & "/" & t[DayStart] )
    )
VAR dt_end =
    IF (
        NOT ISBLANK ( t[YearFinish] ),
        DATEVALUE ( t[YearFinish] & "/" & t[MonthFinish] & "/" & t[DayFinish] )
    )
VAR diff = DATEDIFF ( dt_start, dt_end, DAY )
RETURN
    IF ( categoryIDs > 1 && diff <= 5, "Yes", "No" )