Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hello all,
i have an excel with the below data
Month | Field 1 | |
October | a | |
October | b | |
October | d | |
October | e | |
November | a | |
November | c | |
November | e | |
November | f | |
November | g | |
December | a | |
December | b | |
December | c | |
December | d | |
December | e | |
Oct - Nov - Dec | a | |
Oct - Nov - Dec | e |
is there any formula i can use to select only entried from field 1 which appears in al lthree months (Oct - Nov - Dec) and those would be: "a" and "e" ?
thank you in advane.
Solved! Go to Solution.
Hi @kouliscon ,
In my test, sample table is called 'Sheet12'. Test results are as follows.
To get the middle table visual named as "Filter table", please refer to below measures, you should add measure [check1] to "Visual Level filter" and set its value to 1.
count month no = CALCULATE ( DISTINCTCOUNT ( Sheet12[Month] ), ALLSELECTED ( Sheet12 ) ) count field = CALCULATE ( DISTINCTCOUNT ( Sheet12[Month] ), FILTER ( ALLSELECTED ( Sheet12 ), Sheet12[Field 1] = SELECTEDVALUE ( Sheet12[Field 1] ) ) ) check1 = IF([count field]=[count month no],1,0)
To get the right table visual whose tile is "Final Result", please refer to:
planning and development = VAR temptable1 = FILTER ( ALLSELECTED ( Sheet12 ), CALCULATE ( DISTINCTCOUNT ( Sheet12[Month] ), ALLSELECTED ( Sheet12 ) ) = CALCULATE ( DISTINCTCOUNT ( Sheet12[Month] ), FILTER ( ALLSELECTED ( Sheet12 ), Sheet12[Field 1] = EARLIER ( Sheet12[Field 1] ) ) ) ) VAR temptable2 = SUMMARIZE ( temptable1, Sheet12[Month], "countfield", DISTINCTCOUNT ( [Field 1] ) ) RETURN MAXX ( temptable2, [countfield] )
Best regards,
Yuliana Gu
Hi @kouliscon ,
In my test, sample table is called 'Sheet12'. Test results are as follows.
To get the middle table visual named as "Filter table", please refer to below measures, you should add measure [check1] to "Visual Level filter" and set its value to 1.
count month no = CALCULATE ( DISTINCTCOUNT ( Sheet12[Month] ), ALLSELECTED ( Sheet12 ) ) count field = CALCULATE ( DISTINCTCOUNT ( Sheet12[Month] ), FILTER ( ALLSELECTED ( Sheet12 ), Sheet12[Field 1] = SELECTEDVALUE ( Sheet12[Field 1] ) ) ) check1 = IF([count field]=[count month no],1,0)
To get the right table visual whose tile is "Final Result", please refer to:
planning and development = VAR temptable1 = FILTER ( ALLSELECTED ( Sheet12 ), CALCULATE ( DISTINCTCOUNT ( Sheet12[Month] ), ALLSELECTED ( Sheet12 ) ) = CALCULATE ( DISTINCTCOUNT ( Sheet12[Month] ), FILTER ( ALLSELECTED ( Sheet12 ), Sheet12[Field 1] = EARLIER ( Sheet12[Field 1] ) ) ) ) VAR temptable2 = SUMMARIZE ( temptable1, Sheet12[Month], "countfield", DISTINCTCOUNT ( [Field 1] ) ) RETURN MAXX ( temptable2, [countfield] )
Best regards,
Yuliana Gu
Thank you for your answer.
i tried to do it but i am not so familiar with PowerBI. i am a novice.
can you give me some instructions of what needs to be done? do i need to put a column? i tried to do so and when i paste the first formula it gives me an error.
thank you in advance.
Hi @kouliscon ,
Create measures rather than columns.
I have uploaded the sample .pbix file for your reference.
Best regards,
Yuliana Gu
hello @kouliscon,
if you mean to filter only a and e rows, just use a slicer and filter by date (month)
thank you for your reply,
what i mean is that the data which i want to appear to be only data from field1 whcih are in all 3 months (Oct - Nov - Dec).
how do i input this formula to return only data that is included in all three months and where do i put the formula?
thanks
let me know if this is what you are thinking,
heres the formula :
thanks, i dont know if that is the formula, to applied it woudl be to create a "new quick measure" i suppose?
to understand better of what i want see below
i select the year, month and then it brings me the results which are wrong. my selection is Oct, Nov, Dec and i need to bring as results only the ones that are for the Oct, Nov, Dec. Now it brings if it finds value at any of the three.
what do you expect it to display in the planning and developiment column, and how is planning and development calculated
and what are the two 1's in the first column.
im having alittle difficulty understanding.
"Planing and development" bring results (values) as "count distinct" for the three months.
for the specific column "planning and development" the october is displaying wrong info because the "2" value whcih are basically two distinct values should not both appear. i would like only to appear the value whcih is also appearing at Nov and Dec month.
is it more clear?
not really, you want planning and development column to be all ones not 2,1,1?
you still didnt say how this column is calculated...
the more discriptive you are the easier it is to help.
-Collin
@Anonymous i am looking at a way that the results at the columns (planning and developement as an example) to return only values which are at all three months (oct, Nov, Dec).
not to show/ appear values which are only part of one month.
in the planning and development case the the phone nr. 7089063759 is an entry at October so it shouldnt appear.
@Anonymous yes,
apologies for the late reply but i was away.
can you help me of how this can be done?
@Anonymous yes the data which i want to appear should be only if is on all three months.
correct
OK. thats what ive been needing to understand,
so one option you can do, is creat a column that outputs true if every column in a row is filled and false if not. then creat a slicer that you can select true or false and select true and it will only display data that is complete
let me know if this helps.
Best Regards
Collin
@Anonymous thank you but can you tell me more info of how this can be done in powerBi?
can you show me what columns on your table you need to follow these guidlines
is it the two red circles
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |