Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
siobhanjessica
Frequent Visitor

Power BI DAX Measure for COUNTROWS for text for multiple columns

Hi

 

I have a Power BI Model where I have been creating COUNTROW queries as nearly all of the data in my model is text. As it is not a cost related or numerical model.

 

I am wanting to put MDT outcomes on and calculate how many of the following outcomes there are so I can do visuals. I have more than one summary in the cells, so when I tried to add them it would not let me add it to this measure.

 

2WW Secondary Care =
CALCULATE ( COUNTROWS ('MDT'), 'MDT'[MDT Outcome] = "2WW Secondary Care" )
 
Some of the text would be "2WW Secondary Care & Back to Screening Programme".
 
For some reason it does not like the "|" or "&" symbols so it wouldn't let me work it out. I changed the symbols, then I tried to split the columns into 3 using the data section. How do I calculate how many of each of these headings there are. If I keep the columns split then the text would be: 

Secondary Care None Suspected Cancer

2WW Secondary Care

Rediscuss at Screening MDT

Back to Screening Programme

Secondary Care None Suspected Cancer

2WW WPH Secondary Care

Advice to GP

 

If I combine and have them in 1 column then I need to find the following headings:

 

Secondary Care None Suspected Cancer

Secondary Care None Suspected Cancer & Back to Screening Programme

2WW Secondary Care

Rediscuss at Screening MDT & Back to Screening Programme

2WW Secondary Care & Back to Screening Programme

Back to Screening Programme

2WW Secondary Care & Secondary Care None Suspected Cancer & Back to Screening Programme

2WW WPH Secondary Care

Rediscuss at Screening MDT

Advice to GP & Back to Screening Programme

2WW Secondary Care & Back to Screening Programme & Rediscuss at Screening MDT

 

I want to calculate how many of these summary's there were in total. I also have a 2nd MDT summary that I would like to do if we can figure this out. Hope this makes sense, sorry I am very new to Power BI.

 

Thank you for your help 🙂

2 REPLIES 2
siobhanjessica
Frequent Visitor

@Greg_Deckler 

Hi, thank you for your speedy response.

 

The most important parts are:


1. Sample data as text, use the table tool in the editing bar

MDT Outcomes column needs to be either MDT Outcomes.1, MDT Outcomes.2, and MDT Outcomes.3 if the columns are split. Then the information I need to find is the following info:

Secondary Care None Suspected Cancer

2WW Secondary Care

Rediscuss at Screening MDT

Back to Screening Programme

Secondary Care None Suspected Cancer

2WW WPH Secondary Care

Advice to GP

MDT Outcome

 

(See table below)

 

I need to know how many of each of these have been requested following the MDT.

 

Alternatively the information can be split per patient. In which case the column would be MDT Outcome and in the column I would need to find how many of each of the below ones were requested.

Secondary Care None Suspected Cancer

Secondary Care None Suspected Cancer & Back to Screening Programme

2WW Secondary Care

Rediscuss at Screening MDT & Back to Screening Programme

2WW Secondary Care & Back to Screening Programme

Back to Screening Programme

2WW Secondary Care & Secondary Care None Suspected Cancer & Back to Screening Programme

2WW WPH Secondary Care

Rediscuss at Screening MDT

Advice to GP & Back to Screening Programme

2WW Secondary Care & Back to Screening Programme & Rediscuss at Screening MDT


2. Expected output from sample data

I want to put a visualisation together to show how many of each of these were a conclusion from the MDT meetings. For example, out of 200 patients 75 were sent for 2WW Secondary Care.


3. Explanation in words of how to get from 1. to 2.

I want to calculate how many times each MDT Outcome happened. So I thought I would need to COUNTROWS and then specify which text (or mdt outcome) I was calculating. But I do not know how I would do this if the columns are split then I do not know how to include all 3 columns. If the columns are not split then I do not know how to add the values that include an & symbol.

 

Hope this helps get a better idea of what I am trying to do. 

 

Below is sample of when I have split the columns. I do not need to count how many blank cells there are.

siobhanjessica_0-1686749910187.png

Below is a screen shot of the column if I do not split it:

siobhanjessica_1-1686749996529.png

Thanks.

Greg_Deckler
Super User
Super User

@siobhanjessica Maybe use CONTAINS? Not sure I am completely following. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.