Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am use the below to distinct count two column (excluding blank) and trying to add an extra filter from a third column and have the total. Can someone help with the best way to write the measure to include this?
Act ID =
COUNTROWS (
DISTINCT (
UNION (
DISTINCT (FILTER(VALUES('table'[CreatedNo]), 'table'[CreatedNo] <> BLANK() )),
DISTINCT ( FILTER(VALUES('table'[ModifiedCreatedNo]),'table'[ModifiedCreatedNo]<>BLANK()) )
)))
Solved! Go to Solution.
Hi, @Dtrain
I have been busy recently, sorry to reply you late. You need to use sumx() and summarize() function to calculate toal.
Like this:
Total In-store =
SUMX (
SUMMARIZE (
'Table',
[Date],
"a",
COUNTROWS (
(
DISTINCT (
CALCULATETABLE (
UNION (
CALCULATETABLE (
DISTINCT ( 'Table'[AcceptedByCusId] ),
FILTER ( 'Table', 'Table'[AcceptedByCusId] <> BLANK () )
),
DISTINCT ( 'Table'[CreatedByCusId] )
),
'Table'[Auction Type] = "In-Store"
)
)
)
)
),
[a]
)
Other measures are similar, you can refer to my sample below.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
@Dtrain , Try one of the two
Act ID =
COUNTROWS (
DISTINCT (
UNION (
DISTINCT (FILTER(VALUES('table'[CreatedNo]), not(isblank('table'[CreatedNo]) )),
DISTINCT ( FILTER(VALUES('table'[ModifiedCreatedNo]),not(isblank('table'[ModifiedCreatedNo]))) )
)))
or
Act ID =
COUNTROWS (FILTER(
DISTINCT (
UNION (
DISTINCT (FILTER(VALUES('table'[CreatedNo]), not(isblank('table'[CreatedNo]) )),
DISTINCT ( FILTER(VALUES('table'[ModifiedCreatedNo]),not(isblank('table'[ModifiedCreatedNo]))) )
)) , not(isblank('table'[CreatedNo]) ) )))
Sorry, I dont think I explained it correctly. I also have a "gender" colomn that I would like to include. Using your measure I can get the Total which is what I need but I also need to get the results out of the total to split out the "Males" number and also show the total at the bottom.
Hi, @Dtrain
Can you explain the logic of ''total' ?I can't understand some results according to your needs.
If you can upload some insensitive data samples and expected output, I can make a workaround for you.
Best Regards,
Community Support Team _ Janey
Thank you for helping.
what you had highlighted in question in the total relate to "Female" numbers. CreatedNo & ModifiedNo has been filtered to "Male" only and I have updated the title as you can see in the snapshot.
The way I write the measures for each
-------------------------------------------------
Hi, @Dtrain
I need you to express the logic of the result you want in words, not the measure you wrote.
And I need your sample raw data (in table form not in picture). So I can help you modify one.
Or you can try:
Total Male Only =
COUNTROWS (
DISTINCT (
UNION (
DISTINCT (
FILTER (
VALUES ( 'table'[CreatedId] ),
'table'[CreatedId] <> BLANK ()
&& SELECTEDVALUE ( 'table'[Gender] = "Male" )
)
),
DISTINCT (
FILTER (
VALUES ( 'table'[ModifiedId] ),
'table'[ModifiedId] <> BLANK ()
&& SELECTEDVALUE ( 'table'[Gender] = "Male" )
)
)
)
)
)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Sorry I'm still new to this. I have change the data sample to better explain.
Here is the dataset to explain what I'm trying to do.
Auction ID CreatedByCusId AcceptedByCusId Auction Type Amount Date
10656 1133 1144 In-Store 1 4/06/2021
10665 1143 1107 In-Store 1 4/06/2021
10666 1125 In-Store 1 4/06/2021
10674 1133 1117 In-Store 1 4/06/2021
10691 1115 1066 In-Store 1 7/06/2021
10692 1133 Online 5 7/06/2021
10693 1144 Online 5 7/06/2021
10694 1144 In-Store 1 7/06/2021
10695 1133 Online 5 7/06/2021
10696 1133 1066 Online 5 7/06/2021
10697 1133 1125 Online 5 7/06/2021
10698 1144 Online 50 7/06/2021
10699 1116 1122 In-Store 1 7/06/2021
10700 1066 1125 In-Store 1 8/06/2021
10701 1159 1121 In-Store 1 8/06/2021
10702 1121 In-Store 1 8/06/2021
10703 1095 1066 In-Store 1 14/06/2021
10704 1121 Online 5 14/06/2021
10705 1121 1066 In-Store 1 14/06/2021
10706 1159 1121 In-Store 1 14/06/2021
10707 1121 1117 In-Store 1 14/06/2021
10708 1159 1144 In-Store 1 14/06/2021
10709 1144 In-Store 1 15/06/2021
10710 1115 Online 5 15/06/2021
10711 1121 1117 In-Store 1 15/06/2021
11296 1107 1175 Online 5 31/07/2021
11297 1175 1107 Online 5 28/07/2021
11300 1095 Online 5 30/07/2021
11319 1119 1114 Online 5 6/08/2021
11340 1117 Online 5 20/08/2021
11341 1123 Online 5 20/08/2021
11351 1121 1260 Online 5 5/08/2021
I would like to see by Dates in:
1st column - how many unique Customer created a bid "In-Store"
2nd column - how many unique Customer accepted a bid "In-Store"
3rd column- how many unique Customer both created & accepted a bid "In-Store"
4th column- Total unique Customer both created & accepted a bid & both In-Store & Online.
I need to show the total result for each column at the bottom.
I hope this is easier to understand.
Thanks for making the time to help.
Hi, @Dtrain
Sorry I just saw your addition, I basically understand your needs, if you can upload sample data in table format for me to test, I can solve it faster.
I will get back to you tomorrow.
Best Regards,
Community Support Team _ Janey
I keep on getting this error when creating a table. What am I doing wrong?
Still giving me the error when trying to inserting the table. any other suggestion?
Hi, @Dtrain
I have been busy recently, sorry to reply you late. You need to use sumx() and summarize() function to calculate toal.
Like this:
Total In-store =
SUMX (
SUMMARIZE (
'Table',
[Date],
"a",
COUNTROWS (
(
DISTINCT (
CALCULATETABLE (
UNION (
CALCULATETABLE (
DISTINCT ( 'Table'[AcceptedByCusId] ),
FILTER ( 'Table', 'Table'[AcceptedByCusId] <> BLANK () )
),
DISTINCT ( 'Table'[CreatedByCusId] )
),
'Table'[Auction Type] = "In-Store"
)
)
)
)
),
[a]
)
Other measures are similar, you can refer to my sample below.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Thanks you so much for your support.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |