Hopefully a simple Pivot Question on counting values

[MyWeeLola]

Good morning

I have a table that I have unpivoted to give me 3 columns

Date Attribute and value

I am trying to create a calculated column which counts the number of times the attribute appears in a slicer date range.

I created a measure MyScore = Calculate(Count('TblMarch2024Piv'[Attribute]),'tblMarch2024Piv'[Value]=1)

I then displayed a table with the column Attribute and the measure. All is good, however I need the MyScore as a calculated column.

All is good means that if the attribute appears 40 times in the time period, then it displays 40 beside the attribute. 

I then copied the measure and created a calculated column. 

Now i have my new column counting the number of dates where the value = 1 putting that into the column then adding them all together.

What this means is that if an attribute appears 3 times in one day, then it records 3 distinct records for that day, each with a score of three. it then adds them together to give me a calculated column value of 9 for that attribute.

How do I fix my code to make the calculated column only show the sum of the times the attribute appears in the time period?

I look forward to any help.

Lola

[Anonymous]

Hi @MyWeeLola 

Thank you very much vojtechsima and Angith_Nair for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

First, filter the rows with "Value" of 1.

If you want to count the unique occurrence of each attribute on different dates, you can de-duplicate the data before grouping it.
Select the Date and Attribute columns, right-click, and select "Remove Duplicates".

Select Group By. In the dialog box that is displayed, select Attribute as the attribute column. Select "Count Rows" in "Actions"

Here is the result.

Regards,

Nono Chen

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

[MyWeeLola]

@Anonymous 

Good morning, thank you for taking the time to reply. When I do this, then I lose the ability to sort by date. Is there a solution where i can do this and sort by date?

Lola

[Anonymous · Accepted solution]

Hi @MyWeeLola 

Could you tell me how you would like it sorted by date?

I have some assumptions. If you want to sort attributes based on the earliest date they appear, you can create a new calculate column to query the minimum date for each Attribute:

Start DATE = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALL('Table'),
        'Table'[Attribute] = EARLIER('Table'[Attribute])
    )
)

Then create the new calculate column:

MyScoreColumn = 
CALCULATE(
    DISTINCTCOUNT('Table'[Date]),
    'Table'[Value] = 1,
    ALLEXCEPT('Table', 'Table'[Attribute])
)

You can get:

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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

[Angith_Nair]

Hi @MyWeeLola 

Use this DAX:

MyScore_Column = 
VAR AttributeValue = 'TblMarch2024Piv'[Attribute]
VAR DateRange = 
CALCULATETABLE(
    VALUES('TblMarch2024Piv'[Date]),
    'TblMarch2024Piv'[Value] = 1,
    ALLEXCEPT('TblMarch2024Piv', 'TblMarch2024Piv'[Attribute])
)
RETURN
    DISTINCTCOUNT(DateRange) 

[MyWeeLola]

@Angith_Nair Thank you for the response. The code is struggling with the final distinctcount(daterange) as it is looking for a column reference as the value. Any thoughts?

[Angith_Nair]

Use COUNTROWS instead of DISTINCTCOUNT.

[MyWeeLola]

@Angith_Nair 

Thank you for the response. The returned values are significantly higher than what is in the table. I notice that you have set a VAR for attributeValue, yet i do not see where it is used. Could this be the reason?

Lola

[vojtechsima]

Hi, @MyWeeLola ,
you won't do that in DAX unless you create new physical table.

Here's how you can do it in PQ:
Before:

after:

The code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMDJR0lEyVIrVwSJgRFCFMUEBUxSBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [attribute = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"attribute", type date}, {"value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"attribute"}, {{"sum_value", each List.Sum([value]), type nullable number}})
in
    #"Grouped Rows"

group by settings:

[MyWeeLola]

@vojtechsima 

Thank you for the response. I am not sure what to do with that code. 

I have done very little in advanced editor. I already have a source, or is this for a brand new table, if so how do i poplulate it?

I do appreciate your assistance, and any help with this would be great.

Lola

[vojtechsima]

Hello, @MyWeeLola ,
well you don't have to rescue my code, you just click it, you find Group BY button and do as followed and that'S it really.

You will do the changes on your unpivoted table.

[MyWeeLola]

@vojtechsima 

Thank you for the response. I think I see the issue.

I mentioned i have three columns, Date, Attribute and Value. I see where you are going with the grouping. I missed a comma which probably made you think i had 2.

How I do preserve the ability to search by date using your method?

Lola