Re: Counts not working in a measure.

nabullock · ‎05-02-2023

I am able to calculate the difference between two columns, but am not able to count those results. I have a single table that list values and I need to count the number of increases and decreses, beyond a %, within a time period. I am able to Return "Diff" to a table visual and I need to cont those values as below.

Diff30CTest = 

VAR First30W = 

    CALCULATE(
        MIN(AllWeights[Date]),
        FILTER(
            AllWeights,
            AllWeights[Date] >= [sDateLess30] &&
            AllWeights[Date] <= [selectDate] &&
            AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])
        )
    )

VAR FirstValue =
    CALCULATE(
        MIN(AllWeights[Value]),
        FILTER(
            AllWeights,
            AllWeights[ManPK] = MINX(
                FILTER(
                    AllWeights,
                    AllWeights[Date] = First30W &&
                    AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])
                ),
                AllWeights[ManPK]
            )
        )
    )

VAR Last30W = 
    CALCULATE(
        MAX(AllWeights[Date]),
        FILTER(
            AllWeights,
            AllWeights[Date] >= [sDateLess30] &&
            AllWeights[Date] <= [selectDate] &&
            AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])
        )
    )

VAR LastValue =
    CALCULATE(
        MAX(AllWeights[Value]),
        FILTER(
            AllWeights,
            AllWeights[ManPK] = MAXX(
                FILTER(
                    AllWeights,
                    AllWeights[Date] = Last30w &&
                    AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])
                ),
                AllWeights[ManPK]
            )
        )
    )
VAR CompareFisrtToLast = IF([Last30W] <> [First30W],DIVIDE(([Last30W]-[First30W]),[First30W]),BLANK())
VAR Diff = If(ISBLANK([First30W]),BLANK(),CompareFisrtToLast)
VAR DiffAbove5Percent = IF(Diff > 0.05, 1, 0)
VAR DiffBelowNeg5Percent = IF(Diff < -0.05, 1, 0)
RETURN

I have tried bothe CountX and SUMX ..

So I think that my issue is that "Diff" is returning a rows and I am trying to count those, but DAX expect to count the rows of a table.

v-shex-msft · ‎05-03-2023

HI @nabullock,

According to your description, it seems like common multiple date fields analysis requirement. For this scenario, you can take a look at the Greg’s blog ‘start date’, ‘end date’ part if helps:

Before You Post, Read This: start/end date
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

nabullock · ‎05-08-2023

I don't believe that it is an issue with the dates becuse I am able to correctly show value differents (DIFF30) between the first value (First30W) and the last value (Last30W) within the date range. I just need to count those. If I needed to count those that were +/- 4%, in the table below, it would be 3, 2 are a loss and there is 1 gain.

v-shex-msft · ‎05-09-2023

Hi @nabullock,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

nabullock · ‎05-09-2023

ManPKClientIdDateValue

6062977-4/10/2023 9:02:31 AM	6062977	4/10/2023 9:02:31 AM	146.6
6062977-4/9/2023 6:23:00 AM	6062977	4/9/2023 6:23:00 AM	147.6
6218981-4/10/2023 8:40:00 AM	6218981	4/10/2023 8:40:00 AM	222
5708571-4/9/2023 2:32:20 PM	5708571	4/9/2023 2:32:20 PM	178
6476371-4/9/2023 1:59:00 PM	6476371	4/9/2023 1:59:00 PM	190
4135433-4/10/2023 1:46:18 PM	4135433	4/10/2023 1:46:18 PM	165.6
6927085-4/10/2023 4:01:18 PM	6927085	4/10/2023 4:01:18 PM	212.8
5311211-4/10/2023 12:25:29 PM	5311211	4/10/2023 12:25:29 PM	114
6927085-4/9/2023 2:27:18 PM	6927085	4/9/2023 2:27:18 PM	214.8
4919777-4/10/2023 1:39:00 PM	4919777	4/10/2023 1:39:00 PM	138.2
902441-4/9/2023 11:07:00 AM	902441	4/9/2023 11:07:00 AM	170.3
75093-4/10/2023 12:26:00 PM	75093	4/10/2023 12:26:00 PM	152.1
117631-4/10/2023 3:41:44 PM	117631	4/10/2023 3:41:44 PM	191.4
5445896-4/9/2023 12:40:07 PM	5445896	4/9/2023 12:40:07 PM	90.6
5870295-4/10/2023 1:38:00 PM	5870295	4/10/2023 1:38:00 PM	150
1156178-4/10/2023 8:26:00 AM	1156178	4/10/2023 8:26:00 AM	134.8
6447938-4/10/2023 1:59:00 PM	6447938	4/10/2023 1:59:00 PM	157.2
2326074-4/10/2023 9:49:00 AM	2326074	4/10/2023 9:49:00 AM	89.8
5541675-4/9/2023 12:44:00 PM	5541675	4/9/2023 12:44:00 PM	162.6
2665577-4/10/2023 9:32:00 AM	2665577	4/10/2023 9:32:00 AM	146.2
217101-4/10/2023 2:29:00 PM	217101	4/10/2023 2:29:00 PM	122.1
3874130-4/10/2023 1:46:00 PM	3874130	4/10/2023 1:46:00 PM	87
6875632-4/10/2023 12:59:26 PM	6875632	4/10/2023 12:59:26 PM	136.6
6875632-4/9/2023 1:36:28 PM	6875632	4/9/2023 1:36:28 PM	136.2
1859058-4/10/2023 10:40:51 AM	1859058	4/10/2023 10:40:51 AM	68.8
1755831-4/10/2023 12:30:00 PM	1755831	4/10/2023 12:30:00 PM	103.4
1755831-4/9/2023 12:29:00 PM	1755831	4/9/2023 12:29:00 PM	102.6
6588919-4/9/2023 8:25:00 AM	6588919	4/9/2023 8:25:00 AM	149
651941-4/10/2023 4:21:00 PM	651941	4/10/2023 4:21:00 PM	231.3
6817428-4/9/2023 11:42:00 AM	6817428	4/9/2023 11:42:00 AM	180
6817428-4/9/2023 11:38:00 AM	6817428	4/9/2023 11:38:00 AM	180
73253-4/10/2023 6:51:00 AM	73253	4/10/2023 6:51:00 AM	176
73253-4/9/2023 3:37:50 PM	73253	4/9/2023 3:37:50 PM	174.6
1235954-4/10/2023 9:23:00 AM	1235954	4/10/2023 9:23:00 AM	129.4
3023295-4/9/2023 1:38:42 PM	3023295	4/9/2023 1:38:42 PM	206
310659-4/10/2023 1:39:00 PM	310659	4/10/2023 1:39:00 PM	154.2
4909727-4/10/2023 1:39:00 PM	4909727	4/10/2023 1:39:00 PM	198.1
39304-4/9/2023 12:50:00 PM	39304	4/9/2023 12:50:00 PM	153.2
5870295-4/9/2023 11:07:00 AM	5870295	4/9/2023 11:07:00 AM	150.1
3991466-4/9/2023 11:07:00 AM	3991466	4/9/2023 11:07:00 AM	120.1
5031618-4/9/2023 11:07:00 AM	5031618	4/9/2023 11:07:00 AM	191.8
6043027-4/9/2023 8:12:31 AM	6043027	4/9/2023 8:12:31 AM	103
6043027-4/9/2023 8:00:00 AM	6043027	4/9/2023 8:00:00 AM	103
2620808-4/9/2023 3:21:47 PM	2620808	4/9/2023 3:21:47 PM	110
109874-4/9/2023 12:47:00 PM	109874	4/9/2023 12:47:00 PM	202
5256861-4/10/2023 1:50:27 PM	5256861	4/10/2023 1:50:27 PM	114
6755073-4/10/2023 1:28:47 PM	6755073	4/10/2023 1:28:47 PM	140
3782332-4/9/2023 1:39:08 PM	3782332	4/9/2023 1:39:08 PM	142
3783573-4/10/2023 9:59:00 AM	3783573	4/10/2023 9:59:00 AM	180
1653374-4/10/2023 9:02:00 AM	1653374	4/10/2023 9:02:00 AM	163.8
3783573-4/9/2023 11:02:00 AM	3783573	4/9/2023 11:02:00 AM	186
4626363-4/9/2023 1:36:08 PM	4626363	4/9/2023 1:36:08 PM	173.4
6679851-4/10/2023 5:51:29 PM	6679851	4/10/2023 5:51:29 PM	213
1617436-4/10/2023 9:29:02 AM	1617436	4/10/2023 9:29:02 AM	137
1617436-4/9/2023 1:48:23 PM	1617436	4/9/2023 1:48:23 PM	137
6218981-4/9/2023 8:38:00 AM	6218981	4/9/2023 8:38:00 AM	220
6655103-4/10/2023 9:20:00 AM	6655103	4/10/2023 9:20:00 AM	176
6847152-4/9/2023 1:03:06 PM	6847152	4/9/2023 1:03:06 PM	123.1
6037649-4/9/2023 9:30:00 AM	6037649	4/9/2023 9:30:00 AM	132
6715213-4/10/2023 1:53:00 PM	6715213	4/10/2023 1:53:00 PM	184.2
4324338-4/10/2023 8:46:32 PM	4324338	4/10/2023 8:46:32 PM	146
6798498-4/10/2023 9:32:00 AM	6798498	4/10/2023 9:32:00 AM	150.9
6379466-4/9/2023 1:07:00 PM	6379466	4/9/2023 1:07:00 PM	130.6
6241398-4/10/2023 9:22:00 AM	6241398	4/10/2023 9:22:00 AM	236
6241398-4/9/2023 9:22:00 AM	6241398	4/9/2023 9:22:00 AM	236.4
5855751-4/10/2023 1:52:00 PM	5855751	4/10/2023 1:52:00 PM	116.8
6165827-4/10/2023 1:49:00 PM	6165827	4/10/2023 1:49:00 PM	122.6
5855751-4/10/2023 1:13:00 PM	5855751	4/10/2023 1:13:00 PM	116.8
6847152-4/10/2023 10:26:00 AM	6847152	4/10/2023 10:26:00 AM	124.6
6847152-4/9/2023 1:06:00 PM	6847152	4/9/2023 1:06:00 PM	125.6

nabullock · ‎05-09-2023

Trying to count the loss and gain columns which all values come from this measure because the date is selected by the user

Diff30 =

VAR First30W =

CALCULATE(

MIN(AllWeights[Date]),

FILTER(

AllWeights,

AllWeights[Date] >= [sDateLess30] &&

AllWeights[Date] <= [selectDate] &&

AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])

)

VAR FirstValue =

CALCULATE(

MIN(AllWeights[Value]),

FILTER(

AllWeights,

AllWeights[ManPK] = MINX(

FILTER(

AllWeights,

AllWeights[Date] = First30W &&

AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])

),

AllWeights[ManPK]

)

VAR Last30W =

CALCULATE(

MAX(AllWeights[Date]),

FILTER(

AllWeights,

AllWeights[Date] >= [sDateLess30] &&

AllWeights[Date] <= [selectDate] &&

AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])

)

VAR LastValue =

CALCULATE(

MAX(AllWeights[Value]),

FILTER(

AllWeights,

AllWeights[ManPK] = MAXX(

FILTER(

AllWeights,

AllWeights[Date] = Last30W &&

AllWeights[ClientId] = SELECTEDVALUE(AllWeights[ClientId])

),

AllWeights[ManPK]

)

Var CompareFisrtToLast = IF(LastValue <> FirstValue,DIVIDE((LastValue-FirstValue),FirstValue),BLANK())

Var Diff = If(ISBLANK(FirstValue),BLANK(),CompareFisrtToLast)

RETURN

Diff
-----------------------------------

Loss02% = If([Diff30] <= -.002,1,"")

------------------------------------------

Gain02% = If([Diff30] >= .002,1,"")

Counts not working in a measure.

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024