CODEWITHSUNDEEP
powerbidax
ray
ray

Reputation: 7

In Dax, how can i count values in one column that equal the value of another column?

I have two columns, i want a table that shows the number of "Assign Date" in "Week Start" so for "Week Start" of 1/1/2022 it should be 0, for "Week Start" of 1/7/2022, it should be 2, and it should be 1 for 1/14/2022 and 1/21/2022.

I have two date column

Week Start Assign Date
1/1/2022 1/8/2022
1/8/2022 1/8/2022
1/15/2022 1/15/2022
1/22/2022 1/22/2022

I want one date column and one count column

Week Start Assign Count
1/1/2022 0
1/8/2022 2
1/15/2022 1
1/22/2022 1

I am very new to DAX and i assume that i am over complicating the solution but i can't figure out where to start. Because i am learning DAX, i would like to get this in a DAX measure.

Upvotes: 0

Views: 2523

Answers (2)

Jos Woolley
Jos Woolley

Reputation: 9062

Or this measure:

Assign Count :=
VAR ThisWeekStart =
    MIN( Table1[Week Start] )
RETURN
    0
        + COUNTROWS(
            FILTER(
                ALL( Table1 ),
                Table1[Assign Date] = ThisWeekStart
            )
        )

which you can place in a visual together with the Week Start field.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

There may be more efficient M-Code, but what I did here was to use List.Accumulate to count the number of entries that were in the correct range: >=Week Start and <Week Start + 7 days

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week Start", type date}, {"Assign Date", type date}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Assign Count", 
        each List.Accumulate(
                #"Changed Type"[Assign Date],
                0,
                (state, current)=> 
                    if current >=[Week Start] and current < Date.AddDays([Week Start],7) then state +1 else state)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Assign Date"})
in
    #"Removed Columns"

enter image description here

Upvotes: 0

Related Questions