missing value

[koorosh]

Hello Experts, Have a list that shows partners enrolled in courses, how to make a report that shows missed courses by each partner.

[amitchandak · Accepted solution]

@koorosh , Create new table like

New table =except(crossjoin(all(Table[partner]),all(Table[Course Name])), selectcolumns(Table,"partner", Table[partner], "Course Name",Table[Course Name]))

https://docs.microsoft.com/en-us/dax/except-function-dax

[FarhanAhmed · Accepted solution]

cross join expect 2 inputs ,,,, you are including both columns in ALL statement... separate both columns

CrossJoin( all(Sheet1[Partner]), all(Sheet1[Course Name]))

[FarhanAhmed]

1- you need to separate Course from Orignal table, i.e. a table that contains all courses. From this table you can create course table like this.

Course = SUMMARIZE(Partner,Partner[Course])

2- Then join this table with your main table.

3- Create a matrix visual

4- Put Partner from partner table in Rows

5- Put Course from course table(new summarized table) in columns

6- create a measure for missed count

Missed Course = IF(COUNT(Partner[Course])>0,BLANK(),1)

7- Drop this measure in Values and see the results

[amitchandak · Accepted solution]

@koorosh , Create new table like

New table =except(crossjoin(all(Table[partner]),all(Table[Course Name])), selectcolumns(Table,"partner", Table[partner], "Course Name",Table[Course Name]))

https://docs.microsoft.com/en-us/dax/except-function-dax

[koorosh]

Why get error Amit?

[harshnathani]

Hi @koorosh ,

You need to add a   )   after Sheet1[CourseName]

Create a Table

T2 = except(crossjoin(all('Table'[User]),all('Table'[CourseName])),selectcolumns('Table',"partner", 'Table'[User], "Course Name",'Table'[CourseName]))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[FarhanAhmed · Accepted solution]

cross join expect 2 inputs ,,,, you are including both columns in ALL statement... separate both columns

CrossJoin( all(Sheet1[Partner]), all(Sheet1[Course Name]))

[koorosh]

The result is wrong,

In the new table "arad" should be shown with "B" and "C" course.

[harshnathani]

Hi @koorosh ,

Your Sheet1 Table in the Visualization Pane should have values from New Table.

Here in the screenshot the values are selectd from the sheet1 table.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[koorosh]

Thanks to all gentlemen, Amit, Farhan, Harsh.

[koorosh]

Hi, Again Experts, why partner name slicer does not work for both tables?

[koorosh]

In the main table (sheet1) arad enrolled in "A" and "D" course. In the new table, we are looking for missed courses for arad!

[harshnathani · Accepted solution]

HI @koorosh ,

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[koorosh]

New_Table = EXCEPT(CROSSJOIN(all(Sheet1[Course Name]),all(Sheet1[Partner])),

SELECTCOLUMNS(Sheet1,"partenr",Sheet1[Partner],"course",Sheet1[Course Name]))