Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help Summarizing a Measure Flag

Hi All, 

I am trying to compare files dynamically to see if an employees department has changed. Currently, this is my setup 

 

 

1) Calendar1 - Date Table

2) Workday - Aggregation of Workday reports, delivered weekly
3) File List - Lists each unique file, and assigns it an index

 

From there, im creating measures to dynamically get the Business based on the Current selected month, and the prior Month (by subtracting the current months index by one):

 

Current Month = var SelMonth = SELECTEDVALUE(Workday[File Name])
var currentindex = CALCULATE( VALUES('Workday FIle Dates'[Index]), 'Workday FIle Dates'[File Name] = SelMonth)
return CALCULATE(SELECTEDVALUE('Workday'[Extract Single Business Unit]), 'Workday FIle Dates'[Index] = currentindex)
Prior Month = var SelMonth = SELECTEDVALUE(Workday[File Name])
var currentindex = CALCULATE( VALUES('Workday FIle Dates'[Index]), 'Workday FIle Dates'[File Name] = SelMonth)
var priorindex = currentindex -1
return CALCULATE(SELECTEDVALUE('Workday'[Extract Single Business Unit]), 'Workday FIle Dates'[Index] = priorindex, ALL( Workday[File Name]), ALL('Workday FIle Dates'[File Name]))

 

Next, Im taking these two values and comparing them, to create a flag of 1 or 0 if they do not match

 

TransferCheck = IF(AND([Current Month] <> [Prior Month], [Prior Month] <> ""), 1, 0)

 

 Note: I can definitely combine this all into one measure, but for troubleshooting it was easier to separate. 

 

Im trying to now get the sum (or count of employees) that transferred by business unit (the business unit they started in). However, i cannot simply do a CALCULATE() with a count, as power BI wont allow me to compare a measure to a column. Are there any workarounds for this? Is there a better way to be doing this entirely?

 

Please help! 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Based on what I got so far, You should able to use values or addcolumns and summarize for that

     

    Example 

    sumx(Values(Table[emp]),[TransferCheck])

     

    or

    sumx(addcolumns(summarize(Table,Table[emp], Table[Single business unit]),"_1",[TransferCheck]),[_1])

     

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi Anonymous 

    because not having your sample file, so I create a sample, in my sample, I have 2 tables,

    then I want to compare values in the two table, e.g.

    Tabletest = 
ADDCOLUMNS (
    ADDCOLUMNS (
        DISTINCT (
            UNION (
                SUMMARIZE ( Table1, Table1[namet1] ),
                SUMMARIZE ( Table2, Table2[namet2] )
            )
        ),
        "s1",
            CALCULATE (
                MIN ( Table1[valuet1] ),
                FILTER ( ALL ( Table1 ), Table1[namet1] = EARLIER ( Table1[namet1] ) )
            ),
        "s2",
            CALCULATE (
                MIN ( Table2[valuet2] ),
                FILTER ( ALL ( Table2 ), Table2[namet2] = EARLIER ( Table1[namet1] ) )
            )
    ),
    "s3",
        IF ( AND ( [s1] <> [s2], [s1] <> BLANK () ), 1, 0 )
)

     

     

    result:

     

    hope it helps!

    Any question, please let me know. Looking forward to receiving your reply.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.