Need help with simple filtering on my dataset in power bi

[kakashi-jk3]

Hello PowerBi community!

I'm really new to using powerbi and I'm trying to do a couple filtering on my tables so I can visualize my data.

Some of the filtering is described under here.

I've been using countless of hours trying to figure out how to solve these filtering, but it seem like powerBi desktop is only able to do simple filtering (the one on the right side). So I wonder if some experts here maybe know how I can solve these filtering?

I managed to write a DAX function to retrieve only the last (newest) report, to a new table.

But I can't seem to get this to work.

Example: Tablename: report

The columns in the table report is defined in []

X is an int number.

We want to retrieve all the project with report where:

It should be possible to take overview reports of all projects that:

• [MarginLastMonth] less than X% (if it's possible to get this int as user input in front end. it would be nice, or else we would have to specify it before we embedd)
• [MarginLastMonth] is less than [AdjustedMargin]
• [eacHours] is X% times larger than [adjustedMargin]
• [MarginLastMonth] has reduced X month in a row.
• [eacHours] has increased more than [adjustedTimeBudget]  "X" month in a row
• No report exist for the [expectedEndDate]. (Also there is no report uploaded, which should have been uploaded for the last month)

An aggregated report aggregates data from last reporting for each project included in the report. The report must contain the following fields:

• Weighted average of [adjustedMargin], where weight is based on [eacHours] (Here I found something about using SUMX, but I didn't manage to retrieve a correct answer)
• Weighted average of [MarginLastMonth], where weight is based on [eacHours] (Here I found something about using SUMX, but I didn't manage to retrieve a correct answer)

Best regards.

Hope someone would be able to help me out in this one. 

[Greg_Deckler]

In general, create a measure in the following format:

Measure = CALCULATE(SUM(Table[Column],FILTER(Table,[Column]>[Column]))

For example, specifics will depend on the data and particular measure.

[kakashi-jk3]

@Greg_Deckler

Could you give me an example for the criteria over? Not sure if I understood it, since i couldnt manage to get it to work.

[Anonymous]

@kakashi-jk3

Sorry for not answering. Will get back to you tomorrow.

[kakashi-jk3]

@Anonymous

Did you have time? 🙂

[Anonymous]

@kakashi-jk3

I have a meeting in 1 hour, which I need to prepare for. After that meeting I will look into your problem. In the mean time, could you provide me with sample data? I'll make DAX measures for each of your desired KPIs.

[kakashi-jk3]

@Anonymous the pbix models?

[Anonymous]

Yea or an excel with identical data. If data is sensitive, you can perhaps mask it, or somehow hide the sensitive information. 

[kakashi-jk3]

The only problem is that its connected to our azure database by basic, is it any possible to remove that. while keeping the tables?

By default we use azure SQL, does it work to just download the excel for database? Will the queries you write work for the azure sql format like the excel?

[Anonymous]

what you can do is to:  go to the Data button in the menu on the left -> Left-click and choose copy table -> Paste into excel (do for both tables) -> attach excel to your reply. Then, later on, I'll write some dax measure, which you can just copy paste into your pbix.

Does this make sense? I'll gladly elaborate, if you are in doubt of anything.

[kakashi-jk3]

I sent you a message @Anonymous! 🙂 

Thanks

[Anonymous]

Hi @kakashi-jk3

Can you tell a little more about how your data is stored? Possibly provide me with a table example of how your data is stored.

From what you've descrived, it should be overcomeable to create the measures, you've listed. I just need some more information first.

Best,

Martin 

[kakashi-jk3]

@Anonymous Thank you for your answer. 

Here is the table and the columns. Project and report has projectCode as relationship.

At the moment these powerBi is doing direct query (and not the import one, since I can't find a way to get the live update on dataset to work with import.).

Here is how the data is stored for each column in the table report.

Please tell me if you need to know anything else!

Best regards

[Anonymous]

@kakashi-jk3

The solution to some of your problems is the following:

This should be a calculated column in report table:

 eacHours is X% times larger than adjusted margin = 'Report'[eacHours] / 'Report'[adjustedMargin]

This should be a calculated column in project table:

 Has a done report = IF(CONTAINS(RELATEDTABLE(report);report[status];"utkast");0;1)

This should be a calculated column in your report table:

Month = FORMAT(report[createdDate]; "YYYY-MM")

This should be a calculated column in report table:

MarginLastMonth is less than AdjustedMargin = IF(report[marginThroughLastMonth]<report[adjustedMargin];1;0)

To solve the last 2 measures I need data for another month. 

[Anonymous]

@kakashi-jk3 Thanks for the reply.

And what does one row represent in report-table and project-table? Does it represent one project?

[kakashi-jk3]

@Anonymous sorry for answering so late, the relationship is that one projects can have many reports. While one report can have one project.

Which means one row in report means one report for a specific projectCode.

one row in project means one project.