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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kouliscon
Helper I
Helper I

formula to select data from field 1 which appears in October, November, December

hello all,

 

i have an excel with the below data

 

MonthField 1 
Octobera 
Octoberb 
Octoberd 
Octobere 
Novembera 
Novemberc 
Novembere 
Novemberf 
Novemberg 
Decembera 
Decemberb 
Decemberc 
Decemberd 
Decembere 
   
Oct - Nov - Deca
Oct - Nov - Dece

 

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.

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @kouliscon ,

 

In my test, sample table is called 'Sheet12'. Test results are as follows.

1.PNG2.PNG

 

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)

3.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

18 REPLIES 18
v-yulgu-msft
Employee
Employee

Hi @kouliscon ,

 

In my test, sample table is called 'Sheet12'. Test results are as follows.

1.PNG2.PNG

 

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)

3.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

1.PNG

 

I have uploaded the sample .pbix file for your reference.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

@kouliscon 

 

 

Capture.PNGlet me know if this is what you are thinking, 

 

heres the formula :

 

Result = IF(Table1[Month] = "Oct - Nov - Dec",Table1[Field 1],"null")
 
regards,
Collin

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.

 

Capture.PNG

Anonymous
Not applicable

@kouliscon  

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?

Anonymous
Not applicable

@kouliscon 

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.

 

Capture1.PNG

Anonymous
Not applicable

@kouliscon 

 

so you only want results if there is data from every month in a single column

 

-Collin

@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

Anonymous
Not applicable

@kouliscon 

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?

Anonymous
Not applicable

@kouliscon 

can you show me what columns on your table you need to follow these guidlines

 

is it the two red circles

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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