need a measure which includes all the weeks submission data in a month

[kollasv]

Hi all, i need to create a measure that is resources list of 100 % submitted timesheets for all weeks in a month, 

for Eg, in the picture resource A, C and E were submitted resource for all the weeks in the july month, i need their data , please help me to create a measure, please help me...

Note: Since it is a live data and i should not unpivot the column and also it includes from 2021 to till data data and it is updating everyday .

[Anonymous · Accepted solution]

Hi @kollasv ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Unpivot other columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4uLSpNzMkpLUFJLZsTrRSkZAESc8qtG5ID3GQIYzxTabAEVcsNiAxUJTIMOVMgtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S.no = _t, #"Resoure Name" = _t, #"7/3/2022" = _t, #"7/10/2022" = _t, #"7/17/2022" = _t, #"7/24/2022" = _t, #"7/31/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S.no", Int64.Type}, {"Resoure Name", type text}, {"7/3/2022", type text}, {"7/10/2022", type text}, {"7/17/2022", type text}, {"7/24/2022", type text}, {"7/31/2022", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Resoure Name", "S.no"}, "Dates", "Submitted?"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([#"Submitted?"] = "submitted"))
in
    #"Filtered Rows"

2. Create a measure as below:

Flag = 
VAR _alldates =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Dates] ), ALLSELECTED ( 'Table' ) )
VAR _rdates =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Dates] ) )
RETURN
    IF ( _rdates = _alldates, 1, 0 )

3. Create a table visual and apply visual-level filter with the condition (Flag is 1)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. And what data source you are connecting to? And which connectin mode? Import, Direct Query or live connection? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523