Forum Discussion

kasiak's avatar
kasiak
Frequent Visitor
4 years ago
Solved

Help with DAX calculation

Hi!

 

I'm a new one here & don't have much experience with DAX, so I'd like to ask for your help.

 

I have two tables that contains Staff Name - each table has various data (dates, current task that staff is working on, client name, client ID number & many, many more). The request is to show as a final result: if Staff Name from table A occurs in table B - then that Name from table B should be shown. Unfortunately there's no staff ID.

What's more in Table A - names are divided by grades in columns - I mean:

 

column A - Manager; column B - Senior; Column C - Junior

Staff Name;                ; Staff Name         ; Staff Name

Staff Name etc.            Staff Name           Staff Name etc.

 

Whereas in Table B all names are in one column no matter the grade:

column A:

Staff Name

Staff Name etc.

 

Of course there're duplicates, not unique values.

 

Another issue is:

IF in table A in one of the column the value equals = "yes" - total count per staff name from table B should be the result.

Same issues are with other columns --> IF value equals... & so on.

 

I'm not quite sure if I'm able to achieve it without any Staff ID/unique values.

All I have is unique client ID number, but I don't think it helps.

 

I'd really appreciate your help. I more info is needed - just let me know.

Thank you!

Kasia

 

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    First step would be to unpivot the data in table A.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JTEtT8EvMTTVU0kHiGaHwjJVidZDVmqDImqLwzJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Manager = _t, Senior = _t, Junior = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Manager", type text}, {"Senior", type text}, {"Junior", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"
    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    First step would be to unpivot the data in table A.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JTEtT8EvMTTVU0kHiGaHwjJVidZDVmqDImqLwzJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Manager = _t, Senior = _t, Junior = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Manager", type text}, {"Senior", type text}, {"Junior", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"
    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
  • kasiak's avatar
    kasiak
    Frequent Visitor
    4 years ago

    Thank you. I did it another way (Unpivot selected columns) 🙂 had difficulties with this code😅