Not filtering the column results

[samioberoi]

Hi All,

I am trying to simply sum the hour values from from Comm_wrk_hrs column in Table_Eng and change these hours to days and filter it by months of the Date Dimension table to give me the sum values as per days of each resource name at the monthly level, which seems to be working fine. The DAX i tried to use here is as below and it seems to be working fine.

Comm_wrk = calculate(
                       sum(Table_Eng[comm_wrk]), ALLSELECTED(Dates[Month Number]))/7.5

However, when i try to sum the hours of Ass_work_hrs column from the Table_Ass and  change it to days and use the months column from date dimension table in the filter, it gives the total sum and doesn't filter it at the resource_id level when i put resource_id and Ass_work_hrs in the table visual columns. Below is the DAX i use for it, but doesn't work for this column ASS_Work_hrs of Table_Ass table.

Ass_Work_hrs= calculate(
                       sum(Table_Ass[Ass_work_hrs ), ALLSELECTED(Dates[Month Number]))/7.5

It is very likely there may be something wrong with the relationship and the relationship model i use here is as:-

Table_Com , Table_Eng & Table_Ass were my three fact tables and in order to create a dimension table between the Table_Eng & Table_Ass i tried to create a dimension table Table_Ass (2) by just keeping the Resource ID column  in it and removing the duplicates and adding in the Index column. I thought it may work, but didn't.

Can someone please help me on this? Will be much appreciated.

Thanks in advance.

Sam

[MattAllington]

Ok, great. The next things you can do to help me help you are:

• please rename your tables so they have meaningful names. Eg Sales, Expenses, Hours, Calendar. Single noun names are best where possible. No need for prefixes such as "table" and "dim"
• what is the table "dim dummy" for?
• when you refer to a column in your post, please refer to it as TableName[Column name] as I don't know which table the column comes from and it makes a difference.
• when describing the problem, please create a table or matrix that has the relevant columns from the dimension tables on the rows/columns of the visual, and add any test measures into the visual
• share any formulas for measures in the visual, or dependent measures referenced inside displayed measures

[samioberoi]

Hi Matt,

Apologies for not being to able to reply soon due to me being out of sorts. 

Also, sorry for the messed up data model. I have tried to tidy it up a bit and have named the tables differently. To be honest, the problem i had before is more or less resolved now. The problem was with the table called combined which had zero's in it and of course those were classed as duplicates, hence it was creating that table as a fact table rather than a dimension table. As soon as i removed the zeros from that table, it came up with the right results. However, i am facing a new problem here. I now have to sum the values from for example a column A in the table called Combined based on a condition where the column B names don't contain word like 'Computer' and it has to be filtered then by each month, where i was trying to use months column filter in the slicer, but the Combined Table and Calendar Table are not linked while both are dimension tables. The DAX i was trying to use are as:

Final_DAX= calculate(
           sum(combined[column A]),

           Filter(combined, NOT(Containsstring(Combined[column B],"Computer"))))

Column A = sum(Engagements[Column_work])/7.5

Final_DAX is of course dependent on this simple calculated column called Column A, which is coming from Engagements table and that is linked to the Calendar Table as well as combined table as showing below.

Is there any way the Calendar table can filter the Combined Table? Hopefully i am not making it too complicated in explaining it here.

Thanks for all your effort to help me out.

Looking forward to hear from you soon.

Regards,

[MattAllington]

It's very difficult to help you when the table is called "combined" and the column is called "column A".  Without context, I can't understand what you are trying to do.  What data is in this table and all the columns?  Generally, I would not use a partial string match in a measure. If your formula about "computers" is important, then I would normally expect a dedicated column that can be used to filter the data. Maybe a product hierarchy, or similar. 

[samioberoi]

Hi,

Let me try to explain it a bit further. It is resources data calculating the working hours for each resource, which just works out by a simple calculation of sum of values from hours column in Engagements table and dividing it by 7.5 to change hours to days. Now, as the sum function is working fine in combined data table in the column A for each resource and filters perfectly by the months from the Calendar table. The other part i have to calculate now is to aggregate those values together from  a column A which is now in the table called Combined, and this total value should now be based on a condition where the other column called Devices names does not contain  a word like 'Computer'. So, it should sum up all the values from column A  from combined Date for all the devices , which are named in the Device Names column in the same Combined Data table except for the device names called Computers, and it  then has to be filtered then by each month, where i was trying to use months column filter in the slicer, but the Combined Table and Calendar Table are not linked while both are dimension tables. The DAX i was trying to use are as:

Final_DAX= calculate(
           sum(combined[column A]),

           Filter(combined, NOT(Containsstring(Combined[column B],"Computer"))))

Column A = sum(Engagements[Column_work])/7.5

Hope i am able to explain it properly this time.

Thanks

[MattAllington]

I can see you have 2 bidirectional filtering relationships in your model. Do you know why they are there?  If the answer is no, then you should start there. I would delete the 1:1 relationship and set the other bidirectional relationship to single direction, 1 side filters many side. 

Secondly, do you know why you are using ALLSELECTED? If not, get rid of it and start with

measure = sum(Table_Eng[comm_wrk])/7.5

always set up a visual and write simple measures. You only need calculate if the natural filtering behaviour doesn't work for some reason. 

[samioberoi]

Hi Matt,

Thank for your response. Yes, there was the bidirectional relationships, but that was inactive. I was trying to use that differently before, using the Userelationship function in DAX. That is not needed now and i have deleted that and modified the model as below.

Two ways can help me in resolving my query. I either need to sum all the hours from the Ass_work_hrs column where all the ResourceID's from Table_Ass match up with the ResourceID's from the Table_Comm and due to the reason that both Table_Comm & Table_Ass are fact tables, i tried to create a bridge table between the two called Dim Dummy table, but it still didn't work and doesn't filter the values by months, although i tried to use Related function in DAX and didn't work between the Table_Comm & Table_Ass.

The other thing i can think of it working as is, it can sum up the hours values from Ass_work_hrs column from Table_Ass table and should filter it by ResourceName, and ResourceName is in three tables Table_Res, Table_Comm and Table_Eng as well as should filter by date from the Dimdate table.

Definitely there is something wrong in relationships from my end.

The way i filter on Name and month above in the visual and it brings that particular person's name & Resource ID in the table visual with the correct value of  Sum of ResEngDays4, but the two measures named as ProjAss_Meas and other one named as just Measure for a test, these totals are not filtering out correctly at the ResourceName level.

There must be a solution for this.

Thanks & regards,

Sam