cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Count rows of a summarize table with blank values

I have a client base with two measures: Sellout Value and Sellout Value Last Year, like below:

Sellout Value = SUM(SELLOUT_TABLE[SELLOUT_VALUE])

Sellout Value Last Year = CALCULATE([Sellout Value],PREVIOUSYEAR(CALENDAR[Date]))

An IF measure was created to categorize clients to be rescued, following the logic of those who bought last year but didn’t buy this year.

Client to Rescue = IF([Sellout Value]<=0&&[Sellout Value Last Year]>0,"YES","NO")

The challenge here is to create a measure that counts and consolidate the number of clients which is categorized as a client to be rescued. The measure below was created but the return is resulting as blank.

Clients to Rescue = COUNTROWS(FILTER(ADDCOLUMNS(SUMMARIZE(SELLOUT_TABLE, SELLOUT_TABLE [CLIENT_CODE]), "Sellout Value", [Sellout Value], " Sellout Value Last Year", [Sellout Value Last Year]),[Client to Rescue]="YES"))

Is there any fix in the DAX formulas used or in the base to result the count correctly?

1 ACCEPTED SOLUTION
Community Support

Hi  @Higoricardo ,

Here are the steps you can follow：

1. Create measure.

``````Flag =
var _table1=
SUMMARIZE(ALLSELECTED('Table'),[Client Code],"Measure",[Client to Rescue])
var _table2=
FILTER(
_table1,[Measure]="YES")
return
IF(
MAX('Table'[Client Code]) in SELECTCOLUMNS(_table2,"test",[Client Code]),1,BLANK())``````
``````Clients to Rescue =
CALCULATE(DISTINCTCOUNT('Table'[Client Code]),ALLSELECTED('Table'))``````

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

3 REPLIES 3
Community Support

Hi  @Higoricardo ,

Here are the steps you can follow：

1. Create measure.

``````Flag =
var _table1=
SUMMARIZE(ALLSELECTED('Table'),[Client Code],"Measure",[Client to Rescue])
var _table2=
FILTER(
_table1,[Measure]="YES")
return
IF(
MAX('Table'[Client Code]) in SELECTCOLUMNS(_table2,"test",[Client Code]),1,BLANK())``````
``````Clients to Rescue =
CALCULATE(DISTINCTCOUNT('Table'[Client Code]),ALLSELECTED('Table'))``````

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

Solution Supplier

Hi @Higoricardo ,

So, I'm assuming you want to get the number of clients that would have been YES client to rescue. You should be able to achieve this using the following formula.

``````Number of Clients to Rescue =
COUNTROWS(
FILTER(
VALUES( SELLOUT_TABLE[CLIENT_CODE] ),
[Client to Rescue] = "YES"
)
)``````

Not to sure on your model structure etc. but based on what I see above this should work. Let me know how you get on.

Hope it helps,

Kris

New Member

Hi @kriscoupe !

Thank you for answering, but it didn't work. It's returning just 1 client and as we can see in the base, there are more than one client categorized as to be rescued.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors